CodeIgniter Forums
Help with Mysql Query - Subindex_string + group_concat - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Help with Mysql Query - Subindex_string + group_concat (/showthread.php?tid=79178)

Help with Mysql Query - Subindex_string + group_concat - tektion - 05-05-2021

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 !

RE: Help with Mysql Query - Subindex_string + group_concat - InsiteFX - 05-05-2021

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


RE: Help with Mysql Query - Subindex_string + group_concat - tektion - 05-06-2021

(05-05-2021, 08:54 PM)InsiteFX Wrote:
PHP Code:
$query = $db->getLastQuery();
echo (string)$query; 

Thanks but that's not the issue at all Smile 

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

RE: Help with Mysql Query - Subindex_string + group_concat - php_rocs - 05-06-2021

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

RE: Help with Mysql Query - Subindex_string + group_concat - tektion - 05-06-2021

(05-06-2021, 07:24 AM)php_rocs Wrote: @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()

RE: Help with Mysql Query - Subindex_string + group_concat - php_rocs - 05-06-2021

@tektion,

Have you tried the query by using the query binding method instead (https://codeigniter.com/user_guide/database/queries.html?highlight=query%20bind#query-bindings)?

RE: Help with Mysql Query - Subindex_string + group_concat - iRedds - 05-06-2021

(05-06-2021, 08:49 AM)tektion Wrote:
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.

RE: Help with Mysql Query - Subindex_string + group_concat - tektion - 05-06-2021

(05-06-2021, 12:24 PM)php_rocs Wrote: @tektion,

Have you tried the query by using the query binding method instead (https://codeigniter.com/user_guide/database/queries.html?highlight=query%20bind#query-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 .

RE: Help with Mysql Query - Subindex_string + group_concat - tektion - 05-06-2021

(05-06-2021, 05:57 PM)iRedds Wrote:
(05-06-2021, 08:49 AM)tektion Wrote:
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 :\ 


RE: Help with Mysql Query - Subindex_string + group_concat - php_rocs - 05-07-2021

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