Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Google Sign In with OpenID

No results for query ()->count () when ordering by derived columns

edited December 2013 in Framework

I couldn't figure out why pagination wasn't working when sorting but it seems to have to do with sorting by derived columns. This produces no result for query ()->count ():

select subcategories.*, (SELECT name FROM categories WHERE id=subcategories.category_id) AS category from `subcategories` order by category limit 20 offset 0

(category is not a column in the subcategories table.)

But this does produce a count ():

select subcategories.*, (SELECT name FROM categories WHERE id=subcategories.category_id) AS category from `subcategories` order by category_id limit 20 offset 0

(category_id is a column in the subcategories table.)

Comments

  • I'm confused... :P

  • edited December 2013

    Sorry about that. I'll try to elaborate. I'm starting with the crud-app generated admin handler and view. I've tweaked it to

    // Fetch the items and total items
    $query = subcategories\Subcategory::query (
        'subcategories.*,
        (SELECT name FROM categories WHERE id=subcategories.category_id) AS category');
    $query->order ($sort);
    $items = $query->fetch ($limit, $offset);
    $total = $query->count ();
    

    However, I noticed the pagination by numbers wasn't working for some sorts. It turns out that $total = '' if $sort = 'category', but $total gives the actual total if $sort = 'category_id'.

  • It sounds like the query may not be able to be sorted by the virtual category column. For a total however, you don't need an ORDER BY query, you can simply say:

    $total = subcategories\Subcategory::query ()->count ();
    

    It only needs to be limited by WHERE clauses, since those would reduce the number of results. I usually do my total count as a separate query entirely for this reason.

    For the pager, if you add the ?order=category_id to the url parameter, that ought to work. For example, I generated a "Categories" app with sorting by name or order_by fields here:

    https://gist.github.com/jbroadway/7772962

    Let me know if that helps get it working.

  • edited December 2013

    Yeah, I am using WHERE clauses; I left them out for the sake of simplicity.

    The query sorts fine by category; it just doesn't return a count (). Looking at lib/Model.php, I think that's because the count () function replaces $this->query_fields with count(*), so it generates a column not found error because of the order by category clause.

    Screenshot ordered by category (Error message, pagination not right.)

    Screenshot ordered by category_id (No error, pagination works.)

    It sounds like the easiest thing to do is generate a separate query for count ()

    BTW, I'd like to add sortable columns and search functionality to the admin screen for the crud-apps that elefant generates. Are there templates to modify, or how would I do that?

  • Ah that makes sense to me now. Sounds like a separate count query should solve that.

    To add to the CRUD app generator, take a look in apps/cli. It's generated by apps/cli/handlers/crud-app.php and the templates are all in apps/cli/views/crud-app/. Sortable columns would make a nice addition to the CRUD output, and are something I'd like to add to Elefant's built-in tables too at some point.

  • OK, will do. I've been making the column headings clickable (once for ascending, twice for descending), which is slightly different, I think, than how you did it here: https://gist.github.com/jbroadway/7772962 Do you have any objections in principle?

    Back to count (): since the problem is with the ORDER BY clause, it seems to work to unset $query->query_order like so:

    $items = $query->fetch ($limit, $offset);
    unset ($query->query_order);
    $total = $query->count ();
    

    Kosher?

  • I prefer clickable column headings as well. I only made them separate for quickly mocking it up :)

    For the query_order, I would set it to an empty string instead of unsetting it since that's its default value, but it should work either way. Otherwise that looks good to me!

Sign In or Register to comment.