search server side

rf1234

Using server side with Editor can I perform server searches that search in MySQL table columns that are not sent to the client at all?

My idea is to parse pdf and other office documents after uploading and store the parsed text in database text fields. These fields should be searched server side but they should never be sent to the client.

UPDATE:
I think I have an idea how this could work.
Define the field with the parsed text as "->set(false)" in Editor and have a getFormatter return blank.

Since server side searches the database BEFORE getFormatting this should work, I guess. Am I right?

allan

I perform server searches that search in MySQL table columns that are not sent to the client at all?

Yes, but it would need to be a custom condition, or using a get formatter to return empty data as you say.

Allan

rf1234

Ok, how would you do this with a custom condition? If the condition eliminated the respective column from the result table it wouldn't be searched, would it?

allan

You don't need to include the condition column in the SELECT. So by extension with Editor's libraries, you don't need to have a column as a Field for it to be in the condition.

Allan

rf1234

I understand that: A column used in the WHERE clause does not need to be in the SELECT statement. Right. But I still don't understand how using a WHERE clause will prevent a SELECT field from being sent to the client unless I use a getFormatter that sets it to blank. Particularly if I want the field to be searched on the server. And that is because according to my knowledge WHERE clauses filter out rows but not columns of a table.

tangerine

Hi @rf1234. I must be missing something here.
If you want to search your db for something in your "search_field" column, but not return "search_field" to the client, why doesn't this do it for you:

SELECT some_other_field FROM table WHERE search_field = whatever

?

rf1234

Hi @tangerine,
I have a solution for this anyway. Which is to return an empty field to the client with the getFormatter. This way the respective field gets searched server side but it isn't returned to the client.

I just don't understand @allan 's reply: How can I achieve this result using a WHERE clause? Maybe there is something special that I haven't come across in my almost 30 years of using SQL? Who knows?

I am referring to this by the way in @allan's reply of March 25 above:
"Yes, but it would need to be a custom condition, or using a get formatter to return empty data as you say." With "custom condition" having a link to WHERE clauses.

allan

But I still don't understand how using a WHERE clause will prevent a SELECT field from being sent to the client unless I use a getFormatter that sets it to blank.

But why would you select it at all if you don't want to send it to the client? That's the part I'm not getting. Just use the field in the where condition. Think we might be talking at cross purposes!

Allan

rf1234

The field will contain the parsed content of office and pdf documents. It only exists to facilitate searching. The user only sees the links to the office documents in the browser which are displayed in a different field, not their parsed content. The trick is to search the parsed content field on the server for filtering of returned rows. No user wants to see hardly legible parsed content of office documents; hence the field would be completely useless on the client side, but due its size it would take quite some time to to be sent to the client. In MySQL I will probably use a MEDIUMTEXT or even a LONGTEXT field for the parsed content. I have done this before but without the requirement to be able to search the MEDIUMTEXT or LONGTEXT field server side.

For that reason I want to select the field (so that it gets searched server side) and NOT send it to the client. Or is there a different way to make sure the field gets searched for filtering purposes and still isn't being sent to the client?

The docs behind these links need to be parsed and searched:

allan

I'm afraid I'm still not getting the problem. You have the content you want to search in the database. So apply a WHERE condition to it using a custom query condition. @tangerine's SQL shows exactly the basic idea there. With Editor you might do:

Editor::inst( $db, 'datatables_demo' )
    ->fields(
        Field::inst( 'first_name' ),
        Field::inst( 'last_name' )
    )
    ->where( function ($q) {
        $q->where( 'documentContents', '%...search term...%', 'LIKE' );
    } )
    ->process( $_POST )
    ->json();

documentContents will be searched there, but not sent to the client.

Allan

tangerine

I'm afraid I'm still not getting the problem.

Me neither; though I should probably have offered an Editor example rather than pseudo SQL.

rf1234

Seems to be some confusion here ... I certainly know how to code a WHERE clause with a "LIKE" search in it. I don't want to code the search myself but want Editor's smart search functionality do the work upon user entry of a search string.

But anyway I know how to do it using a getFormatter returning blank combined with Editor server side processing.

allan

I don't want to code the search myself but want Editor's smart search functionality do the work upon user entry of a search string.

Assuming you are using server-side processing, then use $_POST['search']['search'] as the search term with a custom condition.

But good to hear you've got it working now.

Allan