All geek questions in one place

An attempt by the left to join 3 tables, where the id can be the same, I define it using the type

Ok, so I have 3 tables:

of users

CREATE TABLE IF NOT EXISTS `users` ( `user_id` int(11) NOT NULL, `user_username` varchar(25) NOT NULL, `user_email` varchar(100) NOT NULL, `user_password` varchar(255) NOT NULL, `user_enabled` int(1) NOT NULL DEFAULT '1', `user_staff` varchar(15) NOT NULL DEFAULT '', `user_account_type` varchar(20) NOT NULL DEFAULT '0', `user_registerdate` date NOT NULL, `user_twofactor` int(11) NOT NULL DEFAULT '0', `user_twofackey` varchar(255) NOT NULL, `user_forgot_email_code` varchar(255) NOT NULL, `user_emailverified` varchar(25) NOT NULL DEFAULT 'unverified', `user_banned` varchar(25) NOT NULL DEFAULT 'unbanned', `user_has_avatar` int(11) NOT NULL DEFAULT '0', `user_has_banner` int(11) NOT NULL DEFAULT '0' ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1; -- -- Dumping data for table `users` -- INSERT INTO `users` (`user_id`, `user_username`, `user_email`, `user_password`, `user_enabled`, `user_staff`, `user_account_type`, `user_registerdate`, `user_twofactor`, `user_twofackey`, `user_forgot_email_code`, `user_emailverified`, `user_banned`, `user_has_avatar`, `user_has_banner`) VALUES (1, 'fhfhfhf', ' lol@gmail.com ', 'removed', 1, 'admin', 'Business', '2015-07-21', 0, '0', '0', 'unverified', 'unbanned', 1, 0);

the company

 CREATE TABLE IF NOT EXISTS `company` ( `company_id` int(11) NOT NULL, `company_name` varchar(100) NOT NULL, `company_user` int(11) NOT NULL, `company_enabled` varchar(50) NOT NULL DEFAULT 'enabled', `company_has_avatar` int(5) NOT NULL DEFAULT '0', `company_has_banner` int(5) NOT NULL DEFAULT '0' ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- -- Dumping data for table `company` -- INSERT INTO `company` (`company_id`, `company_name`, `company_user`, `company_enabled`, `company_has_avatar`, `company_has_banner`) VALUES (1, 'Rad', 3, 'enabled', 0, 0);

training_company

 CREATE TABLE IF NOT EXISTS `training_company` ( `training_company_id` int(11) NOT NULL, `training_company_name` varchar(100) NOT NULL, `training_company_user` int(11) NOT NULL, `training_company_enabled` varchar(50) NOT NULL DEFAULT 'enabled', `training_company_has_avatar` int(5) NOT NULL DEFAULT '0', `training_company_has_banner` int(5) NOT NULL DEFAULT '0' ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; -- -- Dumping data for table `training_company` -- INSERT INTO `training_company` (`training_company_id`, `training_company_name`, `training_company_user`, `training_company_enabled`, `training_company_has_avatar`, `training_company_has_banner`) VALUES (1, '123', 3, 'enabled', 0, 0), (2, '123', 3, 'enabled', 0, 0), (3, '123', 3, 'enabled', 0, 0);

Each has a profile that has an incremental identifier, so it will have the same identifier, I just define them through the type, so the user will be a user, training will be trained, and the company will be a company, I allow the user to do one of them.

SQL

 SELECT * FROM timeline_status LEFT JOIN users ON timeline_status.timeline_status_user = users.user_id LEFT JOIN timeline_likes ON timeline_status.timeline_status_id = timeline_likes.timeline_likes_main_status LEFT JOIN friends ON timeline_status.timeline_status_user = friends.friends_friend LEFT JOIN user_personal_information ON timeline_status.timeline_status_user = user_personal_information.user_personal_information_user LEFT JOIN following ON timeline_status.timeline_status_user = following.following WHERE timeline_status_enabled = 'enabled' AND timeline_status.timeline_status_type = 'user' AND (timeline_status.timeline_status_user = :status_user OR friends.friends_user = :friend_user) AND (timeline_status_privacy = 'onlyme' AND timeline_status_user = :status_user2 OR timeline_status_privacy = 'public' OR timeline_status_privacy = 'private') GROUP BY timeline_status_id ORDER BY timeline_status_date DESC LIMIT :start, :end

Thus, I would like to select users if type = user, and the string exists in followers and / or friends, choose from companies or undergo training from followers if type = company or training.

My status has a company / user / training ID and type, so I know which table the "user from" selects

my next table;

 CREATE TABLE IF NOT EXISTS `following` ( `following_id` int(11) NOT NULL, `following_user` int(11) NOT NULL, `following_type` varchar(50) NOT NULL, `following` int(11) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; -- -- Dumping data for table `following` -- INSERT INTO `following` (`following_id`, `following_user`, `following_type`, `following`) VALUES (5, 3, 'company', 14), (8, 3, 'training', 1);

timeline status:

 CREATE TABLE IF NOT EXISTS `timeline_status` ( `timeline_status_id` int(11) NOT NULL, `timeline_status_user` int(11) NOT NULL, `timeline_status_privacy` varchar(25) NOT NULL DEFAULT 'public', `timeline_status_type` varchar(25) NOT NULL DEFAULT 'user', `timeline_status_post` text NOT NULL, `timeline_status_date` datetime NOT NULL, `timeline_status_enabled` varchar(25) NOT NULL DEFAULT 'enabled' ) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1; -- -- Dumping data for table `timeline_status` -- INSERT INTO `timeline_status` (`timeline_status_id`, `timeline_status_user`, `timeline_status_privacy`, `timeline_status_type`, `timeline_status_post`, `timeline_status_date`, `timeline_status_enabled`) VALUES (98, 3, 'private', 'user', 'hello', '2015-10-02 16:29:48', 'enabled'), (99, 3, 'onlyme', 'user', 'yo', '2015-10-02 16:29:56', 'enabled'), (100, 3, 'public', 'user', 'fghyjt', '2015-10-02 17:51:28', 'enabled'), (101, 1, 'private', 'training', 'teest..', '2015-10-03 14:26:45', 'enabled'), (102, 15, 'public', 'company', 'hello', '2015-10-06 13:32:30', 'enabled');

So, how can I do this if the next type = company chooses a company, if after tye = training I choose from training, and if the next type = user, save sql as it is at the moment. Since at the moment I am following the company with identifier 1, but there is a user with identifier 1, so I get their statuses.

+5
source share
4 answers

It is best to use the UNION operator to mix them all in 1 table and then query based on type. For example, you can do something like this:

 SELECT f.*, t.training_company_name as name, null as staff, t.training_company_enabled as enabled, t.training_company_has_banner as banner, t.training_company_has avatar as avatar FROM following f INNER JOIN training_company t on f.following_user = t.training_company_user AND f.following_type='training' UNION ALL SELECT f.*, c.company_name as name, null as staff, c.company_enabled as enabled, c.company_has_banner as banner, c.company_has avatar as avatar FROM following f INNER JOIN company c on f.following_user = c.company_user AND c.following_type='company' UNION ALL SELECT f.*, u.user_username as name, u.user_staff as staff, u.user_enabled as enabled, u.user_has_banner as banner, u.user_has avatar as avatar FROM following f INNER JOIN users u on f.following_user = c.company_user AND f.following_type='user'

And from there you will get a view / view that will look like

V_followers(timeline_status_id, timeline_status_user, timeline_status_privacy, timeline_status_type, timeline_status_post, timeline_status_date, timeline_status_enabled, name, staff, enabled, banner, avatar) .

I'm not 100% sure the syntax is MySql-correct, but the idea remains the same.

+5
source

I think you will need another variable to tell you the type in addition to the user id. Then you can wrap both in a CASE statement, for example:

 WHERE CASE WHEN type = 'USER' THEN timeline_status.timeline_status_user = id WHEN type = 'FRIENDS' THEN friends.friends_user = id WHEN type = 'FOLLOWING' THEN following.user = id END
+2
source

In your question you are really asking a few questions)

1) Both company and training_company have a user, so you can join these tables, the left join only accepts the necessary rows from the corresponding tables, and in case you can select the required field:

 select u.user_id, u.user_account_type, case when u.user_account_type = 'Business' then c.company_name else tc.training_company_name end as name_of_company from users u left join company as c on u.user_id = c.company_user left join training_company as tc on u.user_id = tc.training_company_user ;

2) The following table contains both users and companies , so you can do the same:

 select f.following_user, f.following_type, case when f.following_type = 'company' then c.company_name when f.following_type = 'training' then tc.training_company_name else u.user_username end as name from following f left join users u on f.following_user = u.user_id -- here you can add inner joins to followers, friends etc left join company as c on f.following_user = c.company_user left join training_company as tc on f.following_user = tc.training_company_user ;

3) I think that your scheme is quite simple and I am sure that you can use associations to achieve your desires. I would like to emphasize one point if you have a choice: user or company, etc. - use the left connection if you have user restrictions with followers or friends, etc. - use the inner join ... (it should work faster) ...

I hope I understand you correctly, and this information will help you!

+1
source

It looks like you have a situation where you have a concept of an object that can publish statuses, and has other information related to this.

These objects come in three forms; user, company, training_company. Each of these forms may have more specific details.

Have you considered placing all the information about the object in one table:

  • entity - id, type, name, enabled, banner, avatar

Then, the implementation of the has_one relationship to any information related to sub_type:

  • user - entity_id, ... user fields
  • company - entity_id, .. company-specific fields
  • training_company - entity_id .. special fields for company training

Usually you will do one of the following:

  • aggregate statistics regarding objects / statuses - selection from an entity

  • status lists - select from the object

  • aggregate statistics for one specific type - select from the join_ type_table object

  • list inc. detail of one specific type - select from the join_table type_table object

  • single entity information - select from entity_table entity entity

In the rare case when you need to display full information about a list of entities, you can either use a UNION query, or just run one query for entities, and then 3 queries in sub_types WHERE entity_id IN (pulled objects). this data is often paginated anyway, so performance should be good enough.

We used this structure in our projects for a good effect, remember that this will require more complex CRUD operations.

+1
source

Source: https://habr.com/ru/post/1233614/

More articles:


All Articles