2008/9/4 Wolverine <[EMAIL PROTECTED]>: > Let's suppose we have: > User = ['John', 'Jack', 'George'] > Place = ['Paris', 'London', 'Madrid'] > > and Visits (user, place, logdate) like (suppose it's ordered by > ascending date): > Visits = [ > ['John', 'London', date1], > ['Jack', 'Paris', date2], > ['John', 'Paris', date3], > ['George', 'Madrid', date4], > ['Jack', 'London', date5], > ['John', 'London', date6], > ['George', 'London', date7], > ['John', 'Madrid', date8] > ] > > I want to get all Users who are currently visiting London. The result > should be: > (['Jack', 'London', date5], ['George', 'London', date7]). > John is not at London because his newest visit is in Madrid. When > passing argument to query I know ONLY of Place. Nothing more.
I think your problem is a bit similar to the one posted earlier on this list by Chris. Basically what you need to do is make a join between your three tables and aggregate your visit dates with a max() while grouping on the your users. You'll also need where clauses to limit the result set to the requested place. In SQL that would roughly be something like: SELECT users.name, places.name, max(visit.date) FROM users, places, visits WHERE users.id = visits.user_id AND visits.place_id = places.id AND place.name = 'London' GROUP BY users.id; If you post your Elixir models I can try to get the SQLAlchemy query which does that. All the best, Alex Marandon --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "SQLElixir" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlelixir?hl=en -~----------~----~----~----~------~----~------~--~---
