Like query in LiQL

Shivshankar

I've tried a similar query to yours in one of my environments and found that it works properly finding variations as you'd expect. One thing to note though is your syntax, try modifying the query so that AND is uppercase and adding an asterisk wildcard to the end of the string you're matching.

For reference I ran the following test in my environment 

SELECT id,subject FROM messages WHERE subject MATCHES 'test*'

and it returned messages with test, tests, testing, TestUpdate etc in the subject so it does work.

I hope this helps, 

Hi,

I tried as you have  specified, i tried with following queries.

Following are attached screenshots of the queries i have run ,with file name same as index of query. I didn't find any changes in search results putting * wildcard at last. Another query with 'batchupdate' search is giving results , and as 'batchupdate' contains 'batch' so second query should have given some changes in count. 1 without * wildcard2 with * wildcard at end3 with * wildcard at end and beigining4 batchUpdate search

1) select count(*) from messages WHERE board.id='4' AND subject MATCHES 'batch' AND depth=0 limit 1000 offset 0
2) select count(*) from messages WHERE board.id='4' AND subject MATCHES 'batch*' AND depth=0 limit 1000 offset 0
3) select count(*) from messages WHERE board.id='4' AND subject MATCHES '*batch*' AND depth 0 limit 1000 offset 0
4) select count(*) from messages WHERE board.id='4' AND subject MATCHES 'batchupdate' AND depth 0 limit 1000 offset 0

Hi Shivshankar  You can also pass the multiple keywords in the query.

select subject from messages WHERE subject MATCHES ('batch', 'batchupdate') AND depth=0 limit 1000 offset 0

If you will use the below query it will fetch the messages only having subjects like batch, batches, batching etc but not batchupdate. 

select subject from messages WHERE subject MATCHES 'batch'' AND depth=0 limit 1000 offset 0

cant we have some LiQL which works same as v1 search api ?

Hi Shivshankar,

The query that RobertT suggested works fine. I have tried it in my community and returns all messages that have the word 'test' (for example) in the message subject.

SELECT * FROM messages WHERE board.id = 'xyz' and subject MATCHES 'test*'

It returned the following result (sample) -

1. Test

2. First test topic

3. Test topic

Are you looking for something different?

Hi srujanayeruvaka 

FYI asterisk does not make any difference as below queries will give the same count.  But these queries fetch the results having keywords batch, batches, batching etc.  in their subject. 

SELECT count(*) FROM messages WHERE board.id = 'xyz' and subject MATCHES 'batch*'

SELECT count(*) FROM messages WHERE board.id = 'xyz' and subject MATCHES 'batch'

But it would never fetch the message having batchUpdate in their subject which does not seem possible without passing the multiple keywords

select count(*) from messages WHERE subject MATCHES ('batch', 'batchupdate')

Am I right Shivshankar???

Ah VikasB! got it. Thank you for this information :). Here is the corrected query and it worked just fine for me.

SELECT * FROM messages WHERE board.id = 'XYZ' and subject MATCHES ('test*')

I created 2 topics with subjects as 'First Test Topic' and 'Second testtopic' and the above query returned both.

Shivshankar - Can you try this query?

Yes I did, thanks to you for pointing that out VikasB! :)

srujanayeruvaka yes i am looking for different requirement. Can you please check how the lithium serach works , attaching a screenshot of it. It shows "testing" also as one of result whenn we put "test" in serach. I want this kind of functionality. Kind of LIKE function in MYSQL.