Results 1 to 4 of 4

Thread: Sqlite and custom query

  1. #1
    Sencha User
    Join Date
    Jul 2011
    Location
    Holland
    Posts
    98

    Default Sqlite and custom query

    Good day,

    I'd like to use the new Sql Lite proxy, introduced in the 2.1.0 release.

    I'd like to run a custom select query to load data into the specific store. So I can't just run the load() function. Can anyone help me how to load data using my own select query?

    Thank you.

  2. #2
    Sencha User
    Join Date
    Jul 2011
    Location
    Holland
    Posts
    98

    Default

    Actually, I did solve it.

    I wanted to be able to generate my own 'select' queries while loading data. So I created an override.

    It implements two new functions, 'setSql' and 'getSql'. It overwrites the 'selectRecords' function.

    Code:
    Ext.define('App.override.data.proxy.Sql', {    override: 'Ext.data.proxy.Sql',
        
        
           setSql: function(sql) {
            this.customSql = sql;
        },
    
    
        getSql: function() {
            return(this.customSql);
        },
    
    
    
    
        selectRecords: function(transaction, params, callback, scope) {
            var me = this,
                table = me.getTable(),
                idProperty = me.getModel().getIdProperty(),
                sql = 'SELECT * FROM ' + table,
                records = [],
                filterStatement = ' WHERE ',
                sortStatement = ' ORDER BY ',
                i, ln, data, result, count, rows, filter, sorter, property, value;
    
    
            result = new Ext.data.ResultSet({
                records: records,
                success: true
            });
    
    
            if (!Ext.isObject(params)) {
                sql += filterStatement + idProperty + ' = ' + params;
            } else {
                ln = params.filters && params.filters.length;
                if (ln) {
                    for (i = 0; i < ln; i++) {
                        filter = params.filters[i];
                        property = filter.getProperty();
                        value = filter.getValue();
                        if (property !== null) {
                            sql += filterStatement + property + ' ' + (filter.getAnyMatch() ? ('LIKE \'%' + value + '%\'') : ('= \'' + value + '\''));
                            filterStatement = ' AND ';
                        }
                    }
                }
    
    
                ln = params.sorters && params.sorters.length;
                if (ln) {
                    for (i = 0; i < ln; i++) {
                        sorter = params.sorters[i];
                        property = sorter.getProperty();
                        if (property !== null) {
                            sql += sortStatement + property + ' ' + sorter.getDirection();
                            sortStatement = ', ';
                        }
                    }
                }
    
    
                // handle start, limit, sort, filter and group params
                if (params.page !== undefined) {
                    sql += ' LIMIT ' + parseInt(params.start, 10) + ', ' + parseInt(params.limit, 10);
                }
            }
    
    
            console.log(this.customSql);        
            if (this.customSql) {
                sql = this.customSql;
                delete this.customSql; 
            }
    
    
            transaction.executeSql(sql, null,
                function(transaction, resultSet) {
                    rows = resultSet.rows;
                    count = rows.length;
    
    
                    for (i = 0, ln = count; i < ln; i++) {
                        data = rows.item(i);
                        records.push({
                            clientId: null,
                            id: data[idProperty],
                            data: data,
                            node: data
                        });
                    }
    
    
                    result.setSuccess(true);
                    result.setTotal(count);
                    result.setCount(count);
    
    
                    if (typeof callback == 'function') {
                        callback.call(scope || me, result);
                    }
                },
                function(transaction, errors) {
                    result.setSuccess(false);
                    result.setTotal(0);
                    result.setCount(0);
    
    
                    if (typeof callback == 'function') {
                        callback.call(scope || me, result);
                    }
                }
            );
        },
        
    });
    Code:
    store2.getProxy().setSql('SELECT * FROM blabla');
    store2.load();


    The next time 'load' is called, it will use the default behavior and not use the custom query.

  3. #3

    Default Help!

    Hi! Mr.duck

    I'm trying to code a driver that I need to manage a store and run custom sql, I was searching google redirected to your post, but to implement it in my app I jump the following error:

    Uncaught TypeError: Object aeslect_conf.override.data.proxy.Sql has no method 'apply'


    have your script to work properly?


    in advance thank you very much

  4. #4
    Sencha User
    Join Date
    Jul 2011
    Location
    Holland
    Posts
    98

    Default

    Quote Originally Posted by lmendozasv View Post
    Hi! Mr.duck

    I'm trying to code a driver that I need to manage a store and run custom sql, I was searching google redirected to your post, but to implement it in my app I jump the following error:

    Uncaught TypeError: Object aeslect_conf.override.data.proxy.Sql has no method 'apply'


    have your script to work properly?


    in advance thank you very much
    My override does work. Can you post the code of your driver? Or just post the relevant parts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •