Need a quality coder? The author of this blog is now freelancing:
Freelance PHP/CakePHP Web Developer Bristol / Southwest UK

Mysql multiple conditional joins with conditional select

15 May
2010

For my own records but may also be of interest to some…

This statement performs 4 conditional left outer joins and then uses CASE to conditionally select the returned columns as a single group of columns.

So a polymorphic one to one relationship is returned in a single query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$sql = <<<SQL
SELECT *, ELT(Log.action,'$elt_list') AS what,
 CASE WHEN Friend.source_type = '$typeVisitor' THEN 'Anonymous Visitor' WHEN Friend.source_type = '$typeRecipient' THEN 'Referrer' WHEN Friend.source_type = '$typeDiVisitor' THEN 'DI visitor' WHEN Friend.source_type = '$typeFriend' THEN 'Friend' ELSE NULL END as 'source',
 CASE WHEN Friend.source_type = '$typeVisitor' THEN Visitor.title WHEN Friend.source_type = '$typeRecipient' THEN Recipient.title WHEN Friend.source_type = '$typeDiVisitor' THEN DiVisitor.title WHEN Friend.source_type = '$typeFriend' THEN RefFriend.title ELSE NULL END as 'Source.title',
 CASE WHEN Friend.source_type = '$typeVisitor' THEN Visitor.firstname WHEN Friend.source_type = '$typeRecipient' THEN Recipient.firstname WHEN Friend.source_type = '$typeDiVisitor' THEN DiVisitor.firstname WHEN Friend.source_type = '$typeFriend' THEN RefFriend.firstname ELSE NULL END as 'Source.firstname',
 CASE WHEN Friend.source_type = '$typeVisitor' THEN Visitor.surname WHEN Friend.source_type = '$typeRecipient' THEN Recipient.surname WHEN Friend.source_type = '$typeDiVisitor' THEN DiVisitor.surname WHEN Friend.source_type = '$typeFriend' THEN RefFriend.surname ELSE NULL END as 'Source.surname',
 CASE WHEN Friend.source_type = '$typeVisitor' THEN Visitor.email WHEN Friend.source_type = '$typeRecipient' THEN Recipient.email WHEN Friend.source_type = '$typeDiVisitor' THEN DiVisitor.email WHEN Friend.source_type = '$typeFriend' THEN RefFriend.email ELSE NULL END as 'Source.email'
 FROM friends AS Friend
 LEFT OUTER JOIN visitors AS Visitor ON (Visitor.id = Friend.source_id) AND (Friend.source_type = '$typeVisitor')
 LEFT OUTER JOIN visitors AS Recipient ON (Recipient.id = Friend.source_id) AND (Friend.source_type = '$typeRecipient')
 LEFT OUTER JOIN di_visitors AS DiVisitor ON (DiVisitor.id = Friend.source_id) AND (Friend.source_type = '$typeDiVisitor')
 LEFT OUTER JOIN friends AS RefFriend ON (RefFriend.id = Friend.source_id) AND (Friend.source_type = '$typeFriend'),
 log_table AS Log
 WHERE Log.model_id=Friend.id AND Log.type=$tf
SQL;

Comment Form

top