search

cnizzardini / cakephp-datatable

CakePHP Component for interoperability between CakePHP 2.x and jQuery DataTables plugin.
62 stars 49 forks source link

SQL error when searching on virtualField columns. #3

Closed cnizzardini closed 2 years ago

cnizzardini commented 11 years ago

When searching is performed on a virtualField a SQL error is encountered because SQL cannot find the field.

I am not sure how I want to address this problem at the moment. I certainly don't want to muddy the code with what could be a relatively edge scenario.

Work Around: For now in your DataTables aoColumns configuration set {bSearchable: false} on columns which are virtualFields. If you want to search on these fields put them in DataTables (the actual columns and not the virtualField) and in the configuration set {bVisible: false}.

ionas commented 11 years ago

On the topic of Work Around - Furthermore: create a SQL VIEW so you have the field composition on the server. Sadly this means switching tables for your cake model between read (SQL VIEW) and write (original SQL table) operations. Also means one can skip the virtualField alltogather.

The other way to fix this is probably fixing virtualFields of cakephp to also allow comparison (see http://stackoverflow.com/a/5734595 ) -though not sure if this is possible on Postgres and MS SQL Server etc.

Btw. thanks for your awesome Datatables Component...:)

fr0z3nfyr commented 7 years ago

Create the virtual field in your model constructor instead of in the controller action and the search will work normally. See issue #36 for more details.