This is my scenario: I have two different tables: Table 'subscriptions' contains all the information related to subscriptions to user sites, and the 'ratings' table, which contains scores to products of subscribing sites. Simplifying is something like that:
Subscriptions: 'id','user','place','created', etc.
Ratings: 'id','user','product','created',etc.
Now I need mysql to return my most recent 10 items, either a subscription or a rating. So far, the solution found was to make two different calls:
SELECT * FROM subscriptions WHERE ... ORDER BY created LIMIT 10.
With the two results (20 elements), I order multidimensional array by 'created' and get the top 10 with (saving the result in $items):
$items = array_slice($items, 0, 10);
I suspect that this solution is not efficient, because I get it in two calls, and return more elemnts than I need. I've been reading that the UNION operator allows you to select several different tables at once, but with the same number of columns. My question is, what is the most efficient way to join a specific number of elements of different types ordered by a common field (created in my case) in just one call? is ot possible? Thanks a lot, any help is appreciated, Jesús.
UNION is required.
You can learn how to use it here:
Join two tables, then Order By date, BUT combining both tables
It's not explained in the link I posted, but can put a LIMIT on a UNION as well. Combined with an ORDER BY this is what you need. (cred to Frits van Campen)
(SELECT cols FROM subscriptions)
UNION
(SELECT cols FROM ratings);
ORDER BY created LIMIT 10
Modify your query to use a UNION to combine the results and once the results have been combined you can utilize the LIMIT method and ORDER BY for limiting the output to the desired number of records.
To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:
In addition:
To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one.
(SELECT cols FROM subscriptions WHERE ... ORDER BY created LIMIT 10)
UNION
(SELECT cols FROM ratings WHERE ... ORDER BY created LIMIT 10);
ORDER BY created LIMIT 10