MySQL Two databases, order by one column in one case, order by another column in different cases

Question

I have two tables. The first table looks like this:

Table: Entries

     rid |  user id |  title |  whenadded |  public
 -------------------------------------------------- ------------------
     1 212 Example 2012-06-28 1
     2,217 Test Rec 2012-07-05 1
     3 212 Another 2012-07-02 1
     4,212 Unlisted 2012-05-02 0
     5,217 Success 2012-04-08 1
     6,238 Always 2012-04-18 1

Table: Likes

     id |  user id |  rid |  whenliked
 -------------------------------------------------- ----
     1 212 2 2012-07-06
     2 205 1 2012-06-30
     3 212 5 2012-07-04

In the Records table, rid is set as the primary index. In the "Likes" table, id is set as the primary index.

I am using PHP. PHP will provide the MySQL value for use as a reference. I would like to have one MySQL query that will do the following:

Pseudocode:

$userid = 212; $SQL = 'SELECT DISTINCT records.* FROM records,likes WHERE (records.userid = ' . $userid . ' AND records.public = 1) OR (records.id = likes.rid AND likes.userid = ' . $userid . ' AND records.public = 1) ORDER BY likes.whenliked DESC, records.whenadded DESC LIMIT 50;'; 

Take a look at the SQL SQL query I just presented above. This is my attempt to develop the query that I wanted, but it did not achieve what I was looking for; it was pretty damn close, but it was still misordered. The request was designed as best as possible, and I based on what I found looking for a solution on StackFlow and elsewhere on Google.

These are the conditions I'm trying to order:

• All selected entries must be publicly available (records.public = 1).
• If the entry belongs to the user (in this example, 212), then sort the entries by .whenadded entries.
• If the entry does not belong to the user, but it is the entry that the user liked, then order entries like.whenliked.
• Dates will be sorted from newest to oldest.

The end result from the returned query will look like this (remember that when the value is not in the returned data, it is just available for reference, so you see how it is ordered):

     rid |  user id |  title |  whenadded |  public |  whenliked {not incl}
 -------------------------------------------------- ----------------------------------
     2,217 Test Rec 2012-07-05 1 2012-07-06
     3 212 Another 2012-07-02 1
     5,217 Success 2012-04-08 1 2012-06-30
     1 212 Example 2012-06-28 1

Hope this makes sense. Feel free to ask questions, I will clarify what I can.

Thank you in advance for your time, your attention and for this. Even if there is no answer or no solution is found, your time is still greatly appreciated! :)

Answer 1

The field you order does not have to be an existing field in the database. You can also use the field that you define in your choice:

 SELECT IF(records.userid = ' . $userid . ', records.whenadded, likes.whenliked) as date 

Now you can use it in order:

 ORDER BY date DESC 

From MySQL Guide :

A select_expr can be assigned an alias using AS alias_name. The alias is used as the name of the expression column and can be used in GROUP BY, ORDER BY, or HAVING clauses.

Answer 2

I would suggest splitting a query into 2 queries and using UNION instead.

 SELECT * FROM( (SELECT records.rid, records.userid,records.title, records.whenadded as adate FROM records,likes WHERE records.userid = $userid AND records.public = 1 ) UNION (SELECT records.rid, records.userid,records.title, likes.whenliked as adate FROM records,likes WHERE records.rid = likes.rid AND likes.userid = $userid AND records.public = 1) )t ORDER BY adate DESC 

Editorial: See http://sqlfiddle.com/#!2/9a877/4

Answer 3

How about this:

 $userid = 212; $SQL = 'SELECT DISTINCT records.* FROM records,likes WHERE (records.userid = ' . $userid . ' AND records.public = 1) OR (records.id = likes.rid AND likes.userid = ' . $userid . ' AND records.public = 1) ORDER BY records.userid, likes.whenliked DESC, records.whenadded DESC LIMIT 50;'; 

This will separate the entries individually and by user first, and then by date.

Answer 4

According to my comment, here is another request you should look at:

 SELECT records.* FROM records LEFT JOIN likes ON records.rid = likes.rid WHERE records.public = 1 AND COALESCE(likes.userid, records.userid) = $userid ORDER BY COALESCE(likes.whenliked, records.whenadded) DESC; 

It is assumed that you cannot record more than once and ride the weird DISTINCT.

The documentation for COALESCE is here .

This request is not very efficient. If you really have a large database dividing the query in half so that the user indexes it at full capacity and then joins them again, it might be better.