Join two tables and get count in mysql:Example with hasMany relations in sequelize

Consider we have two tables ‘users‘ and ‘comments‘.Each user can have multiple comments so that ‘users‘ and ‘comments‘  have ‘hasMany‘ relation. The foreign key is user_id in the comments table. The structure of both tables are as follows Users id    | name |    email 1    userone    user1@gmail.com 2    usertwo    user2@gmail.com Comments id    | comment        | user_id 1      commentone      1 2      commenttwo      1 3      commentthree    2 4      commentfour     2 5      commentfive      2 6      commentfive      2 As you can see, the user with id 1 has two comments and the user with id 2 has  4 comments. To get all users with their respective number of comments, we can use the following query.

The result will be 1 userone user1@gmail.com 2 2 usertwo user2@gmail.com 4 Sequelize Example   To learn more about sequelize, see the following article https://truetocode.com/orm-using-sequelize-orm-with-express-node-js/418/ For our example, we need to create two model files usersModel.js

  commentsModel.js

  hasMany relation Now we need to define hasMany relations from users to comments, in your db.js file….

How to accurately check date range to prevent overlapping:mysql
Snippets , Requests / July 29, 2018

Q:I have a table with events , and each event has an auditorium id,start_date and end_date.In need to insert a new event in to the table without any overlapping on date range in an auditorium   select IF(count(*)>0, true, false) from events where ‘YOUR_START_DATE‘ <= end_date and ‘YOUR_END_DATE‘ >= start_date; OR select IF(count(*)>0, true, false) from events where ‘YOUR_START_DATE‘  BETWEEN start_date AND end_date or ‘YOUR_END_DATE‘ BETWEEN start_date and end_date and end_date or (‘YOUR_START_DATE‘ < start_date and ‘YOUR_END_DATE‘ > end_date); If true do not proceed with insert if false continue

 

How to change ‘sql_mode’ in mysql
PHP , Uncategorized / July 2, 2018

To see Mysql mode settings,do the following query. Mysql mode is not linked to any particular database. Modes can be set locally to the session (connection), or for the server globally.

This gives results like below   Now to change sql_mode,you need to run query like this

Example Some times after  importing database and changing some table structure,you may get an error like this. This is because NO_ZERO_IN_DATE and NO_ZERO_DATE is enabled in and database has invalid date values. NO_ZERO_IN_DATE The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. Example – ‘2010-00-01’ or ‘2010-01-00’ NO_ZERO_DATE This mode is deprecated This determines whether the server permits ‘0000-00-00’ . NO_ZERO_IN_DATE also depends on whether strict SQL mode is enabled. Removing above modes from sql_mode  lets the server allow invalid dates in the aforementioned format     Or to set it PERMANANTLY, change etc/my.cnf

and restart WHAT is sql_mode Server SQL modes determine what SQL syntax MySQL should support and what kind of data validation checks it should use. This makes it easier to use MySQL in different environments. See more on  list of sql…

Share this page in social media platforms