*= operator in FieldtypeComments

[renobird]

Hi Ryan,

This issue was previously mentioned here.

I'm trying to find pages like so:

$pages->find("comments.text*=SomeStringHere, sort=-created");

I get this error:

Fatal error: Exception: Operator '*=' is not implemented in FieldtypeComments

Running 2.3

[Pete]

Is it not in dev branch as per ryan's post in that topic you linked to then? Guessing you've probably tested that already.

[renobird]

Hi Pete, that was back from October 2012, so I assumed it was pre 2.3.

I'll check the latest.

Thanks.

[renobird]

So, I don't see this in the commits anywhere — could be overlooking it though.

I have a lot going on with this site, and don't want to use the dev branch. 

Do you have a dev install with comments?

If so, do you mind checking this:

$pages->find("comments.text*=SomeStringHere, sort=-created");

Much appreciated.
 

OK, installed the latest dev on a test server, still getting the same error.

Seems this never got implemented. 

Anyone have ideas on a workaround?

Maybe a direct DB query?

[renobird]

Just in case there is anyone else who needs to search comments.

I pieced together some stuff Soma and Ryan had posted in other topics.

Here is a DB query workaround:

$query = $db->query("SELECT pages_id FROM field_comments WHERE data LIKE '%SomeStringHere%' ORDER BY created");
$ids = array();
while($row = $query->fetch_row()) $ids[] = $row[0];
$results = $pages->getById($ids);

[ryan]

I think the issue is that you were specifying "comments.text" rather than just "comments". You don't need to specify a subfield when searching comments. This should already work (and it is implemented in 2.3):

$pages->find("comments*=SomeStringHere, sort=-created");

There isn't actually a "text" field in the DB, which is why your search wasn't working (it's called 'data' instead). I have gone ahead and pushed an update to the dev branch that looks for ".text" and substitutes ".data", so that nobody else should run into that issue. 

[renobird]

Hi Ryan,

I did eventually realize there was no "text" and started using "data" as soon as I went the $db->query route.

Let's say I just need to find comments with a particular email address (Direct DB query works):

$search = $db->query("SELECT pages_id FROM field_comments WHERE email LIKE '%me@tomrenodesign.com%' ORDER BY created");

API gives me an internal server error:

$search = $pages->find("comments.email*=me@tomrenodesign.com, sort=-created");

Is it not possible to specify a subfield when searching comments, or do I need to do something different with the selector?

No worries if this isn't possible, $db->query works just fine — Just clarifying.

[ryan]

'email' isn't in a fulltext index like 'data' is. So the fulltext index operators *= and ~= aren't going to work there. But if searching out a full email address, you are doing an exact match and would just want to use "=", i.e. 

$search = $pages->find("comments.email='me@tomrenodesign.com', sort=-created");

Or for a partial match, use the LIKE operator: 

$search = $pages->find("comments.email%='tomrenodesign', sort=-created");

I haven't tried it, but I think that should work. 

[renobird]

Hi Ryan,

Using '=" works, but "%=" gives and internal server error.

Ultimately I'm using this method to search a new comments field I added to the DB called "notify".

That field contains a comma separated list of email address, so the LIKE operator seems to be the way to go.

This is a non-typical usage for me, so the $db->query method will work.

Thanks for your time responding and explaining.

[ryan]

Using '=" works, but "%=" gives and internal server error.

The "%=" was working for me when I tested it locally before the last reply. And that does translate to a LIKE query. The internal server error means you'd need to turn on debug mode or examine your /site/assets/logs/errors.txt to see what the error was. But what was the full selector? I'm just curious if maybe the value needed to be quoted or something like that. Either way, glad you found a way to get it working. 

[choppingblock]

The "%=" operator also gives me an error,

Exception: Operator '%=' is not implemented in FieldtypePage

also the "*=" and "~=" operators. any idea why that could be?

I'm running PW 2.3.0

[teppo]

@choppingblock: just like the exception says, '%=' isn't implemented with page type fields, as it's not needed there. I've explained this in more detail in another thread.