Help with Mysql Query - Subindex_string + group_concat

Hi guys, 

New to CI and having some trouble with a few more advanced SQL queries . 

I am trying to use substring_index together with group_concat in order to make an array where I have 2 results of each unique group . 

I have tried the following :

Code:

$Array = $MyModel
   ->where('Column3 !=', 0)
   ->where('SUBSTRING_INDEX(GROUP_CONCAT(GROUP)),',1)
   ->orderBy('date', 'DESC')
   ->groupBy('GROUP')
   ->limit(2)
   ->find();

Could someone kindly help me fixing my query syntax ? 

Thanks in advance !

PHP Code:

$query = $db->getLastQuery();
echo (string)$query; 


What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

PHP Code:

$query = $db->getLastQuery();
echo (string)$query; 


Thanks but that's not the issue at all  

The issue is syntax with substring index line , I can't get that part right :\

@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?

@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()

@tektion,

Have you tried the query by using the query binding method instead (https://codeigniter.com/user_guide/datab...y-bindings)?

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()

You are using in the select method not a SQL function, but a php function (php thinks so). Therefore, you get an error.
Turn it into a string and you will be happy.

@tektion,

Have you tried the query by using the query binding method instead (https://codeigniter.com/user_guide/datab...y-bindings)?

Tyvm for the suggestion, seems like the best option for advanced queries indeed as it avoids the need of escaping characters-. 

Code:

$sql = "SELECT SUBSTRING_INDEX(GROUP_CONCAT(id separator ','), ',', 5) FROM `table`";

$this->QueryDb->query($sql);

This works but if possible I would still like to know the proper char escaping to make the query work with my Model, as I am working with arrays and using Models makes life easier .

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()

You are using in the select method not a SQL function, but a php function (php thinks so). Therefore, you get an error.
Turn it into a string and you will be happy.

Thanks, I thought it was escaping characters issue but I already tried using quotes on the function and it's still returning  the same error . 

A working example would be really nice, I'm stuck at this for 2 days :\ 

@tektion,

Try it this way instead (remove the extra comma in the second where statement):

Code:

$Array = $MyModel
   ->where('Column3 !=', 0)
   ->where('SUBSTRING_INDEX(GROUP_CONCAT(GROUP))',1)
   ->orderBy('date', 'DESC')
   ->groupBy('GROUP')
   ->limit(2)
   ->find();