Help with Mysql Query - Subindex_string + group_concat

@tektion,

InsiteFX is trying to get you to output what your query looks like after it is built by the CI query engine.  This might point you in the right direction to fix your issue.  Have you run the query directly in the database to make sure that it works?

Ah right, thanks for pointing that out didn't pay attention since I was focused on the line causing the syntax issue .

Well the problem is the substring_index syntax is not right :

Code:

CRITICAL - 2021-05-06 15:54:50 --> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', = 1
GROUP BY `GROUP`
ORDER BY `date` DESC
LIMIT 2' at line 9

This is the output I get from this query !

I tried replacing it with

Code:

->where(SUBSTRING_INDEX(GROUP_CONCAT('GROUP'),',', 1))

But then I get an invalid function error :

Call to undefined function App\Commands\SUBSTRING_INDEX()


In the meantime I realized after running the query in MySql that you can't use it as a subquery with where statement so I changed it around and this is what works when I run directly in my database :

Code:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(id separator ','), ',', 5)
FROM `mytable`
GROUP BY `GROUP`
ORDER BY `date` DESC

So I need to convert this into valid codeigniter format .

Tried this :

Code:

$MyModel->select(SUBSTRING_INDEX(GROUP_CONCAT('id separator ',''), ',', 5))
                    ->orderBy('date', 'DESC')
                    ->groupBy('GROUP')
                    ->limit(2)
                    ->find();

Which still returns the error :  Call to undefined function App\Commands\SUBSTRING_INDEX()