search and sort function on virtual column

Questionssearch and sort function on virtual column
Harry asked 4 years ago

Hi I have a column that is generated from a sql statement,

$grid->select_command = "SELECT staging.*, COUNT(studies.PMID) AS refcount FROM staging LEFT JOIN studies ON studies.rs_number = staging.rs GROUP BY staging.idstaging";

the column is 'refcount'.

what must I enter in

$col["dbname"] = "staging.refcount";

so this error is addressed:

Couldn't execute query. Unknown column 'staging.refcount' in 'where clause' – SELECT count(*) as c FROM (SELECT staging.*, COUNT(studies.PMID) AS refcount FROM staging LEFT JOIN studies ON studies.rs_number = staging.rs WHERE 1=1 AND `staging`.`refcount` > '0' GROUP BY staging.idstaging) pg_tmp

Thank you

8 Answers
Abu Ghufran answered 4 years ago

Here we need a HAVING clause which is currently not supported.

Harry answered 4 years ago

I am using the refcount column as a count of related records per row.

It would be very useful to be able to find the only the rows in primary table which have related records in the second table or to sort them according to the number of related records.

Is there another logic you can recommend to accomplish this other than altering the structure of the database itself?

Abu Ghufran answered 4 years ago

I'm emailing you latest build that supports having clause function.
All you need is to set:

$col["dbname"] = "COUNT(studies.PMID)";

It will detect aggregate functions and use having instead of where clause.

Harry answered 4 years ago

That works, thank you.

Harry answered 4 years ago

New build has an error in a master detail use.

The error modal is empty. This error occurs:

1. following an edit to a detail record (edit is still made)
2 following a delete to a detail record (delete is still made)
3. in *some* detail collections lists (list is not displayed at all)

When installing the old build the error no longer appears.

Abu Ghufran answered 4 years ago

Closing this ticket as resolved on email.

dvdtgreen answered 4 years ago

Can you also email the latest build that supports having clause function to me ?

Abu Ghufran answered 4 years ago

Emailed.

Your Answer

15 + 0 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

We are sorry that this post was not useful for you!

Let us improve this post!