or_where sql limit?

wyatt121

Hi,

Can I use a sql type LIMIT so that I only get 5 rows returned for each of my or_where below? I only want 5 records returned with status BAN and only 5 records returned with status FRONT.

->where( function ( $q ) {
    $q->where( 'date', 'DATE_ADD( NOW(), INTERVAL -30 DAY )', '>=', false )

->where( function ( $r ) {
            $r->or_where( 'status', 'BAN' );
            $r->or_where( 'status', 'FRONT' );
        } );

} )

Any help is much appreciated...

Regards,

Wyatt

allan

Hi Wyatt,

Not directly I'm afraid. You could use $q->limit(5) but that would limit the full set to just 5 results. There is no way to limit just for one condition. I must confess, I'm not even sure how that would be done in plain SQL - sub-selects with a join?

Allan

wyatt121

Hi Allan,

Here is how I believe it could be done in Mysql.

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Scroll to "Select the top N rows from each group". I would use the date field instead of price used in this example...

I was hoping to avoid such a complex solution. Is it even possible to use this Mysql solution with datatables? Can you think of any workarounds? What would you advise?

Thank you for all your help.

Regards,

Wyatt

allan

Thanks for the link - yes, that's sort of what I was thinking of. Using a UNION is a nice way of doing it.

Unfortunately there is no option to do that in Editor's pre-built libraries. It would require some custom SQL to be used to read data like that. That data can certainly be used in DataTables - but the Editor libraries won't generate it.

Allan

wyatt121

Is there documentation on how to send custom MySQL queries to DataTables using PHP?

zapata

http://datatables.net/forums/discussion/31952/raw-sql-query-for-editor

wyatt121

@zapata. Thank you for your example. @allan stated that raw sql queries are not editable directly. How do I make complex raw sql queries editable?

glenderson

instead of using the provided php scripts, why not just develop your own php page to perform the query and return the data in a json format?

, "ajax": {
url: "/yourpagename.php"
}

Then make "yourpagename.php", open the database, perform all the sql querying that you need, and return it in a JSON format? I use .aspx pages so that's what I do all the time.

allan

How do I make complex raw sql queries editable?

There is no option for that - sorry. Editor needs to "understand" the SQL which is why it is defined using methods such as fields() and where().

You can absolutely create your own SQL read with Editor - the client / server communication is documented in the manual . However, there is no option for what you are looking to do with the pre-built PHP libraries.

Allan