XF 2.0 Finder and Replace

[sbj]

Hello,

bear with me as I am not a developer. I am trying to modify an existing addon (it is out of the scope of the support) and need help using the correct finder.

From what I understand this is a SQL statement SELECT * FROM some_table_name WHERE title LIKE '%?%'
equivalent to this code:

$threads = $finder
            ->where('title', 'LIKE', $finder->escapeLike($threadTitle, '%?%'))

How can I modify this into:

SELECT * FROM ... WHERE REPLACE(title, '-', '') LIKE '%?%'

I want the finder ignoring hyphens. As raw SQL I think the above query would work, but I don't know how to do that for the finder.

Any help is appreciated. Thank you.

 

[S Thomas]

I guess that's what expressions are designed for: https://xenforo.com/community/threads/finder-with-regexp-inxenforo-2.139276/#post-1203545

 

[sbj]

I tried this
->where('title', $finder->expression("REGEXP '.*' ", $threadTitle))
but I get an error. Is the syntax correct?
ErrorException: [E_WARNING] mysqli::real_escape_string() expects parameter 1 to be string, object given
I think I need to escape it like in the example above but what is the right way to do that for this?

 

[DragonByte Tech]

Try this:

$expression = $finder->expression("REPLACE(title, '-', '')", $threadTitle);
$finder->where($expression, 'LIKE', $finder->escapeLike($threadTitle, '%?%'));

Fillip

 

[sbj]

Try this:

I did and I get an error:
InvalidArgumentException: Unknown column verd on XF:Thread

The XF2 development manual says:
The where method can support up to three arguments. The first being the condition itself, e.g. the column you are querying. The second would ordinarily be the operator. The third is the value being searched for.

And I think putting where($expression, means I am trying to query the column expression (the value of it), but in this case it must be "title" and nothing else I think.
I think the 3rd argument must be changed cause that is the part I want to have modified, in my case ignoring hyphens. But I could be wrong of course, you guys know it.

 

[S Thomas]

Did you extend your Thread entity correctly? And your threads table aswell?

 

[sbj]

Did you extend your Thread entity correctly? And your threads table aswell?

Not sure what is there to extend and as I said, it's not my addon. And I believe everything is correct as the addon itself works perfectly.

The whole part looks like this:

        $threadTitle = $this->filter('title', 'str');
        $finder = \XF::finder('XF:Thread');
        $threads = $finder
            ->where('node_id', '=', $nodeId)
            ->where('title', 'LIKE', $finder->escapeLike($threadTitle, '%?%'))
            ->where('discussion_state', '=', 'visible')
            ->where('discussion_type', '<>', 'redirect')
            ->order('post_date', 'DESC')
            ->limit($limit)
            ->fetch();

And the only thing I am trying to change is that the finder shouldn't query the column "title" LIKE "%?%" (whatever I type), but before that it should remove the hyphens from the titles, so the titles can be found without needing to type hyphens. And I believe this raw one-liner would do what I want SELECT * FROM ... WHERE REPLACE(title, '-', '') LIKE '%?%' but there is no documentation how to do that for the finder.

 

[S Thomas]

InvalidArgumentException: Unknown column verd on XF:Thread

Sounds like a custom column to me.

 

[sbj]

I repeat:

The where method can support up to three arguments. The first being the condition itself, e.g. the column you are querying. The second would ordinarily be the operator. The third is the value being searched for.

And when you do what was suggested by DragonByte:

$expression = $finder->expression("REPLACE(title, '-', '')", $threadTitle);
$finder->where($expression, 'LIKE', $finder->escapeLike($threadTitle, '%?%'));

to make it more clear:

$finder->where($expression, 'LIKE', $finder->escapeLike($threadTitle, '%?%'));

So @DragonByte Tech code results us querying a column in the table thread, which doesn't exist.
"verd" comes from what I typed in the addons interface on XF. I could have typed "abc" or "example" and the code above would query the column "abc" or "example". But as we know these columns don't exist in the thread xf_thread.
Hence why I said but in this case it must be "title" cause I want to query the column title.

To sum it up, what is the equivalent to this raw SQL statement:

SELECT * FROM xf_thread WHERE REPLACE(title, '-', '') LIKE '%?%'
So before I query the column title, I want the titles stripped of hyphens, so the query can find titles ignoring hyphens.

It is a one-liner with raw SQL. But I am all saying this with my limited knowledge, I don't understand the finder.

 

[S Thomas]

"verd" comes from what I typed in the addons interface on XF. I could have typed "abc" or "example" and the code above would query the column "abc" or "example". But as we know these columns don't exist in the thread xf_thread.

So maybe you should type "title" in the addons interface? This is tbh out of the scope how people can help without having access to the addon itself. You should really ask in the custom dev area to get help. We can only guess at this point.

 

[S Thomas]

$expression = $finder->expression("REPLACE(title, '-', '')", $threadTitle);

That should be 'title' or ['title'] since it's referring to the column if I'm not mistaken. Works that way.
Actually, not even that, it's straight up not needed

 

[DragonByte Tech]

That could very well be it, I didn’t exactly test it


Fillip