SQLIteStore LEFT JOIN and add new fields to select

[wwwjsx]

Hi !

this my store:

this.store = new Ext.data.SQLiteStore ({
            dbFile      : 'data/unitech.db',
            tableName   : Unitech.Tables.Product,
            baseParams  : { start: 0, limit: this.pageSize },
            idProperty  : 'id',
            autoLoad    : true,
            remoteSort  : true,
            sortInfo    : { field: 'date_added', direction: 'DESC' },
            fields      : [
                { type: 'int',      name: 'id' },
                { type: 'string',   name: 'barcode' },
                { type: 'string',   name: 'name' },
                { type: 'int',      name: 'category_id' },
                { type: 'float',    name: 'price' },
                { type: 'int',      name: 'unit' },
                { type: 'int',      name: 'date_added' },
                { type: 'int',      name: 'date_modified'  },
                { type: 'int',      name: 'total' },
                { type: 'int',      name: 'available' }
            ],
        }); 

How can I add LEFT JOIN and new columns to SQL.

Thanks

[makana]

Create a view and use it as tableName.

[wwwjsx]

Can you show me an example ? I am newbe.

Thank you very much!

[makana]

Hi!

Use a database management tool, like the SQLiteManager addon for firefox, and open your database with it.
Then create a view like:

CREATE VIEW myView
AS
SELECT t1.field1, t1.field2, t2.field3, t2.field4
    FROM myTable1 t1
    LEFT JOIN myTable2 t2
        ON t1.foreignKeyToT2Column = t2.primaryKeyColumn

and apply the view's name (here "myView") to the tableName config option of the store:

this.store = new Ext.data.SQLiteStore ({
            dbFile      : 'data/unitech.db',
            tableName   : Unitech.Tables.Product,
            tableName   : 'myView',
            baseParams  : { start: 0, limit: this.pageSize },
            idProperty  : 'id',
            autoLoad    : true,
            remoteSort  : true,
            sortInfo    : { field: 'date_added', direction: 'DESC' },
            fields      : [
                { type: 'int',      name: 'id' },
                { type: 'string',   name: 'barcode' },
                { type: 'string',   name: 'name' },
                { type: 'int',      name: 'category_id' },
                { type: 'float',    name: 'price' },
                { type: 'int',      name: 'unit' },
                { type: 'int',      name: 'date_added' },
                { type: 'int',      name: 'date_modified'  },
                { type: 'int',      name: 'total' },
                { type: 'int',      name: 'available' }
            ],
        });

You can then use the view to make reading requests to the database via the store.
If you want to do insert, update or delete operations on the store, too, you have to create triggers on your view. E.g.

CREATE TRIGGER myViewTriggerInsert
INSTEAD OF INSERT ON myView
FOR EACH ROW
BEGIN
    INSERT OR REPLACE INTO myTable2 (field3, field4) VALUES (new.field3, new.field4);
    INSERT INTO myTable1 (field1, field2, foreignKeyToT2Column) VALUES (new.field1, new.field2, (SELECT primaryKeyColumn FROM myTable2 WHERE field3 = new.field3 AND field4 = new.field4));
END

See http://www.sqlite.org/lang_createtrigger.html for more information.

Cheers,
makana