1

I have one table (Award) that is linked to another (Person) through intermediate table (AwardPerson). I want to select Awards with associated Persons in one query. Basically my query looks something like this:

      SELECT Award.title
             Person.name
        FROM Award
  OUTER JOIN AwardPerson ON Award.id = AwardPerson.award_id
        JOIN Person ON AwardPerson.person_id = Person.id

Now I want to add pagination, allowing user to specify limit and offset. If I just add

       LIMIT 5 OFFSET 25

I will get less Award rows if I have more than one person associated with one Award. Here's result example:

"Fastest pizza eater" "Bob"
"Fastest pizza eater" "Alice"
"Fastest pizza eater" "Mary"
"Biggest stomach"     "John"
"Best manners"        "Sam"

First award is repeated three times, because there are 3 AwardPerson records for one Award record. That's why for query with LIMIT 5 I got only 3 Award entities. But what I wanted is 5 Awards with no matter how much Persons attached.

Is there any way I can LIMIT and OFFSET query, that has joins with Many-to-Many relationships?

The only solution, I can think of is to always select more than asked (like LIMIT * 3) and then drop extra results. But this approach does not work good with OFFSETs (imagine how do I tweak OFFSET 1000).

Also, there is implicit requirement for this query to work fast :-)

UPDATE

The solution I found in Is it possible to LIMIT results from a JOIN query? is to make subquery which adds new column with some CASE-WHEN-ELSE logic. This approach is too hard to implement using sqlalchemy, so I am thinking about removing offset feature from my application...

UPDATE 2

There is another solution. Making two database queries. First SELECT DISTINCT award.id with applied LIMIT, and then fetch whole request using IN condition.

Community
  • 1
  • 1
peterdemin
  • 516
  • 8
  • 26
  • Why you join it with person if you need awards? What will happen with persons? – Anton M. Oct 15 '15 at 20:13
  • I need both awards and persons. I post-process results in following manner. I go through rows and check if award is the same as previous. If so, I append current person to existing award. Otherwise I create new Award with attached Person. – peterdemin Oct 15 '15 at 20:17
  • What is the expected result? – Lukas Eder Oct 15 '15 at 20:19
  • Updated question with "5 Awards with no matter how much Persons attached." – peterdemin Oct 15 '15 at 20:27
  • If it is mysql you can do group by award.id, mysql will take one person record. May be some others will work too, not oracle, not postgres. – Anton M. Oct 15 '15 at 20:41
  • Thanks for suggestion, but I use MS SQL and sqlite. And I join 4 more tables, so grouping does not work in this case. – peterdemin Oct 15 '15 at 20:58
  • Possible duplicate of [SQL limit SELECT but not JOIN](http://stackoverflow.com/questions/31296244/sql-limit-select-but-not-join) and [Is it possible to LIMIT results from a JOIN query?](http://stackoverflow.com/questions/2465878/is-it-possible-to-limit-results-from-a-join-query?rq=1) – peterdemin Oct 15 '15 at 21:10

0 Answers0