v5, v7, Chart and group_by issue SQLITE3

[platipusica]

Hi Andrew,

looks like when a Lookup field (Category from Products - which is hardcoded value in Product table), is added and THAT filed is used for ie Chart like this:

    var ord = item.task.purchase_order_details.copy({handlers: false});
    ord.open(
        {
            fields: ['id', 'quantity', 'category'], 
            funcs: {quantity: 'sum'},
            group_by: ['category'],
            order_by: ['-quantity'],
            limit: 10
        }, 

The SQL produced is this:

ERROR - no such column: Purchase_Order_Details.Category
Traceback (most recent call last):
  File "/home/dbabic/Downloads/SAML/py38/lib/python3.8/site-packages/jam/execute.py", line 15, in execute_select
    cursor.execute(command)
sqlite3.OperationalError: no such column: Purchase_Order_Details.Category

Error: no such column: Purchase_Order_Details.Category

Error: no such column: Purchase_Order_Details.Category
 command: SELECT "Purchase_Order_Details"."ID", SUM("Purchase_Order_Details"."Quantity") AS "Quantity", Products_231."Category" AS Category_LOOKUP FROM "Purchase_Order_Details" AS "Purchase_Order_Details" OUTER LEFT JOIN "Products" AS Products_231 ON "Purchase_Order_Details"."Product_ID" = Products_231."ID" GROUP BY Products_231."Category", "Purchase_Order_Details"."Category" ORDER BY "Quantity" DESC LIMIT 0, 10

So I investigated further, looks like when Category is added to a table, it is not created in the DB!

This can be observed here:
https://northwind.pythonanywhere.com/

Add a lookup to Purchase_Order_Details, it will display ok on VIEW. But the Chart will fail.

Thoughts?

[platipusica]

I just replicated this issue on Demo:

function show_tracks(item, ctx) {
    var tracks = item.task.tracks.copy({handlers: false});
    tracks.open(
        {
            fields: [ 'artist', 'tracks_sold', 'id'], 
            funcs: { tracks_sold: 'sum' },
            group_by: ['artist'],
            order_by: ['-tracks_sold'],
            limit: 10
            }, 

sqlite3.OperationalError: no such column: DEMO_TRACKS.ARTIST

Error: no such column: DEMO_TRACKS.ARTIST
command: SELECT SUM("DEMO_TRACKS"."TRACKS_SOLD") AS "TRACKS_SOLD", "DEMO_TRACKS"."ID", DEMO_ALBUMS_27_ARTIST."NAME" AS ARTIST_LOOKUP FROM "DEMO_TRACKS" AS "DEMO_TRACKS" OUTER LEFT JOIN "DEMO_ALBUMS" AS DEMO_ALBUMS_27 ON "DEMO_TRACKS"."ALBUM" = DEMO_ALBUMS_27."ID" OUTER LEFT JOIN "DEMO_ARTISTS" AS DEMO_ALBUMS_27_ARTIST ON DEMO_ALBUMS_27."ARTIST" = DEMO_ALBUMS_27_ARTIST."ID" WHERE "DEMO_TRACKS"."DELETED"=0 GROUP BY DEMO_ALBUMS_27_ARTIST."NAME", "DEMO_TRACKS"."ARTIST" ORDER BY "TRACKS_SOLD" DESC LIMIT 0, 10

[platipusica]

V7 also the same, except no JS Error given!

[platipusica]

I think here is a problem in sql.py:

def group_clause(self, query, fields, db_module=None):
...
                    if func:
                        result += '%s."%s", ' % (self.table_alias(), field.db_field_name)
                    else:
                        #result += '%s, %s."%s", ' % (self.lookup_field_sql(field, db_module),
                        #    self.table_alias(), field.db_field_name)
                        result += '%s "' % (self.lookup_field_sql(field, db_module))

When Artist is used for GROUP BY, it works well. However, now I can't use second field for group by.

[platipusica]

Solution:

                    else:
                        #result += '%s, %s."%s", ' % (self.lookup_field_sql(field, db_module),
                        #    self.table_alias(), field.db_field_name)
                        result += '%s, ' % (self.lookup_field_sql(field, db_module))

function show_tracks(item, ctx) {
    var tracks = item.task.tracks.copy({handlers: false});
    tracks.open(
        {
            fields: [ 'artist', 'name', 'genre', 'album', 'tracks_sold', 'id'], 
            funcs: { tracks_sold: 'sum' },
            group_by: ['artist'],
            order_by: ['-tracks_sold'],
            limit: 10
        }, 
..

[platipusica]

The above solution does not work for #322