Custom sql query for a field...
Custom sql query for a field...

Gotten pretty far into the project and everything runs smooth.
I came across one thing though:
Is there an easy way to use a custom "SELECT COUNT(xxx) FROM table WHERE value = xxx" and return this to the client? This field is only to show how many rows that exist in another table with that special value... This field will not be updated or created. Like this for example: If Ihave 3 tables here, authors, genres and authorgenreconn(this is used to bind author and genre together since one author can have many genres), But in other fields I want to show how many books each author has, both total and in different genres.
So I would need something like:
Field::inst(SELECT COUNT(book_id) FROM books where author_id = authors.author_id)
and for example
Field::inst(SELECT COUNT(book_id) FROM books where author_id = authors.author_id AND genre_id = xxx)
Editor::inst( $db, 'authors', 'author_id' )
->field(
Field::inst( 'authors.author_name' ),
Field::inst( 'authors.author_added_date' ),
Field::inst( 'authors.authorstatus_id' )
->options( 'authorstatus', 'authorstatus_id', 'authorstatus_name' ),
Field::inst( 'authorstatus.authorstatus_name' ),
Field::inst( 'authorstatus.authorstatus_label' )
->set (false)
)
->leftJoin( 'authorstatus', 'authorstatus.authorstatus_id', '=', 'authors.authorstatus_id' )
->join(
Mjoin::inst( 'genres' )
->link( 'authors.author_id', 'authorgenreconn.author_id' )
->link( 'genres.genre_id', 'authorgenreconn.genre_id' )
->order( 'genre_name asc' )
->fields(
Field::inst( 'genre_id' )
->options( 'genres', 'genre_id', 'genre_name' ),
Field::inst( 'genre_name' )
)
)
->process($_POST)
->json();
This question has an accepted answers - jump to answer
Answers
Found this: https://datatables.net/forums/discussion/36355
Seems it's not possible to do the count on the server side, but instead I have to send the full arrays to the client and do length function there? That's a lot of unnecessary data being sent, unless you've implemented some functionality since that thread?
You are correct that the Editor PHP libraries do not provide an option to execute SQL functions (such as
COUNT()
). There are basically two options:Editor
PHP class.Mjoin
class and the resulting array's length property.Regards,
Allan
OK, I see... And there's no way to cheat Editor?
Like doing Field::inst on an existing field in the DB I won't use, then before the Editor section getting the Count from a outside function and assign that to Field::inst with getValue?
I assume it's not or you would have thought about it, but one can always ask...
Well, I tried a simple function and it seems to work. I'll try to make a sql query in that function anad see where I end up...
It loooks like this:
Sorry - no. The fact that it can't run functions is fairly base to its database abstraction. It is something I plan to address in future.
The best I can really think of is that you use a VIEW which will perform the more complex query and the Editor libraries can just SELECT against that.
Allan
Ah, great idea! I'll look into that...
Hi,
Can I vote for this functionality as well?
I want to have the table display two fields that are essentially counts of linked tables within the database.
I would like Editor to simply ignore these fields and return the same data for them as was POST'd to it.
Thanks for the vote
. Support for SQL functions is something I'm planning to introduce in a future version (I don't know when it will come yet), but at the moment a VIEW is probably the best workaround.
Allan
How?
I see this as the answer to a number of forum questions, but no explanation of how to actually do that, and all my google searches lead back to these questions...
Assuming you are using the Editor PHP libraries you could just use it's
select
method:Or if you prefer, you can just query the database using whatever database interface you normally use (e.g. PDO).
Allan