1

I have a pretty complex SQL query as shown in this fiddle

SELECT payer_payment.payer_id, 
       Sum(payer_payment.amount)                             AS total_paid, 
       Sum(payer_payment.pays * payments_share.single_share) AS fair_share 
FROM   payers 
       INNER JOIN (payer_payment 
                   INNER JOIN (SELECT payment_id, 
                                      Sum(amount) / Sum(pays) AS single_share 
                               FROM   payer_payment 
                               GROUP  BY payment_id) AS payments_share 
                           ON payer_payment.payment_id = 
                              payments_share.payment_id) 
               ON payers.id = payer_payment.payer_id 
WHERE  payers.user_id  = 1 
GROUP  BY payer_payment.payer_id;

In the fiddle it runs fine on MySQL but when I run it on a SQLite database it either throws an error citing:

(1 no such column: payer_payment.payer_id)

When the column clearly does exist.

or simply returns 0 results, depending on the SQLite implementation (WebSQL vs SQLite.js)

What is the reason for this and is it possible to make my query more database agnostic?

harryg
  • 23,311
  • 45
  • 125
  • 198
  • Please have a look at http://stackoverflow.com/questions/8112936/sqlexception-no-such-column – fvu Mar 19 '14 at 23:34
  • Hmmm, I removed the superfluous brackets but it doesn't seem to help – harryg Mar 19 '14 at 23:39
  • in fact it then breaks it for MySQL – harryg Mar 19 '14 at 23:45
  • Have a look at your Fiddle for SQLite (with a single line at the end): http://sqlfiddle.com/#!5/ccd48/7. Have a look at payers->id. I think SQLFiddle for SQLite is somewat buggy and autoincrement is not working (or, which is more likely - AUTOINCREMENT is called IDENTITY in SQLite) – cha Mar 20 '14 at 00:41
  • Have a look at this Fiddle where I have removed auto_identity: http://sqlfiddle.com/#!5/d9775/2 It works here – cha Mar 20 '14 at 00:49
  • I see your fiddle works on sqlite.js, but not on websql. Additionally the query doesn't work on my local sqlite, either via laravel or phpliteadmin (which I think both utilize the PDO driver). In my laravel app the schema is already built and all the rows have functioning auto-ids. – harryg Mar 20 '14 at 09:13

1 Answers1

1

In SQLite, you get an autoincrementing column by using INTEGER PRIMARY KEY. (And if you rely on the actual values of payers.id in the query, you should give them explicitly.)

You should not try to nest joins when it is not needed:

SELECT payer_payment.payer_id,
       Sum(payer_payment.amount)                             AS total_paid,
       Sum(payer_payment.pays * payments_share.single_share) AS fair_share
FROM       payers
INNER JOIN payer_payment
        ON payers.id = payer_payment.payer_id
INNER JOIN (SELECT payment_id,
                   Sum(amount) / Sum(pays) AS single_share
            FROM   payer_payment
            GROUP  BY payment_id) AS payments_share
        ON payer_payment.payment_id = payments_share.payment_id
WHERE payers.user_id = 1
GROUP BY payer_payment.payer_id;

SQLFiddle

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Many thanks, I shall try this on my local environment but the nested joins is probably the cause. Re the schema, I just made it to test the query on SQFiddle; on my Laravel app the schema is correctly built using Laravel Migrations so will have the auto-increment IDs. – harryg Mar 20 '14 at 10:45
  • It was indeed the nested joins that were the culprit, Thanks again. – harryg Mar 21 '14 at 10:51