Getting the "latest" related record from a SQL DB
abigail at abigail.be
Mon Oct 13 18:33:19 BST 2014
On Sun, Oct 12, 2014 at 05:00:10PM +0100, Andrew Beverley wrote:
> What I failed to say was that as well as doing this for a single artist
> as per my question, I'd like to also be able to do it for several
Ah, the perils of not asking what you want.
> Therefore (I think) I can't use either of those options without
> doing several database queries.
You can using a subselect:
SELECT Artist.*, Album1.name
FROM Artist, Album Album1
WHERE Artist.id = Album1.artist_id
AND Artist.id IN (... list of artist IDs ...)
AND Album1.release_date = (SELECT MAX(Album2.release_date)
FROM Album Album2
WHERE Album2.artist_id = Album1.artist_id);
This time, I actually tested the query.
> I also like to avoid sub-selects as
> MySQL has a tendency to optimise them badly (as Gareth said).
It's much better to measure the performance of the various queries on
your actual data than tossing some out of the window due to "tendencies".
> What I also failed to say was that I'm using DBIC and want to keep
> things database agnostic.
Then you shouldn't start with discarding queries because of possible
performance issues at a specific database.
More information about the london.pm