How to execute a subquery

[ThibaultWUN]

I have a subquery that works well in phpMyAdmin but I cannot make it work in prestashop.

$list = Db::getInstance()->executeS('SELECT id_customer FROM `'._DB_PREFIX_.'customer_group` WHERE id_group LIKE (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)');

What do I have to change for this code to work is Prestashop ? How can I make a subquery ?

[Guest]

Do you want to put this in your previous module?

[Guest]

Need to get a list as a spreadsheet?
Helper List?

You have to describe everything in more detail.

Edited October 13, 2020 by Guest (see edit history)

[Guest]

Make a screen for your module.

[EvaF]

imho the query should look like

$list = Db::getInstance()->executeS('SELECT id_customer FROM `'._DB_PREFIX_.'customer_group` WHERE id_group IN (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)');

(otherwise you can get error if

SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5

returns more the one row)
 

[Rachit Agarwal]

Yes You should go with below query. @EvaF is right.

$list = Db::getInstance()->executeS('SELECT id_customer FROM `'._DB_PREFIX_.'customer_group` WHERE id_group IN (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)');

 

[ThibaultWUN]

  On 10/13/2020 at 2:32 PM, Guest said:

Do you want to put this in your previous module?

Expand  

It is in a different module.

  On 10/13/2020 at 2:33 PM, Guest said:

Need to get a list as a spreadsheet?
Helper List?

You have to describe everything in more detail.

Expand  

I have groups called admin_%companyName% and groups called user_%companyName%.

If a user of the group user_myCompany is logged in, he can only have access to his own data. If a user of the group admin_myCompany is logged in, he must have access to his own data and to the data of all the users of the group user_myCompany. If the current user is in a group admin_%companyName%, I make a substring to get the company name and I create a new string "user_" + $companyName. That is the string I am using in my subquery.

A user can only be in ONE group of the type admin_%companyName% or ONE group of the type user_%companyName%.

[ThibaultWUN]

  On 10/13/2020 at 7:23 PM, EvaF said:

imho the query should look like

$list = Db::getInstance()->executeS('SELECT id_customer FROM `'._DB_PREFIX_.'customer_group` WHERE id_group IN (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)');

(otherwise you can get error if

SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5

returns more the one row)
 

Expand  

Ok thanks, I'm going to try like that although my subquery should not return more than one row...

[EvaF]

I don't argue - because i don't know your data - I only say that

the construction:
 

...WHERE id_group LIKE (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)

is very unusual and leads to mysql-error if result of subquery returns more than 1 row - that's all

That's a reason  I have suggested:

...WHERE id_group IN (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)

 

[ThibaultWUN]

  On 10/15/2020 at 8:02 AM, EvaF said:

I don't argue - because i don't know your data - I only say that

the construction:
 

...WHERE id_group LIKE (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)

is very unusual and leads to mysql-error if result of subquery returns more than 1 row - that's all

That's a reason  I have suggested:

...WHERE id_group IN (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)

 

Expand  

And you are right, it looks cleaner that way. But it still does not work. What's weird is that I tried this query in phpMyAdmin and it works...

SELECT id_customer FROM ps_customer_group WHERE id_group IN (SELECT id_group FROM ps_group_lang WHERE name LIKE 'mygroup' AND id_lang=5)

And I tried to make two queries instead of one but it does not work either.

$idGroup = Db::getInstance()->executeS('SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE 'mygroup' AND id_lang=5');

It also makes my website crash... And again, it works in phpMyAdmin !

Edited October 15, 2020 by ThibaultWUN (see edit history)

[EvaF]

I am not sure, if you write correct 'mygroup'

 in the case of group:  user_%companyName%  

thus in specific case you want to find f.e. user_AAACompany

don't  forget that in mysql  underscore character "_" is considered as wildcard  - i.e. you should write

LIKE 'user\_AAACompany'

and result query should look like:

SELECT id_customer FROM ps_customer_group WHERE id_group IN (SELECT id_group FROM ps_group_lang WHERE name LIKE 'user\_AAACompany' AND id_lang=5)

 

Edited October 15, 2020 by EvaF (see edit history)

[Rachit Agarwal]

  On 10/15/2020 at 8:32 AM, ThibaultWUN said:

$idGroup = Db::getInstance()->executeS('SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE 'mygroup' AND id_lang=5');

Expand  

as per my understanding, mygroup should be in double quotes, so can you please try with below query:

$list = Db::getInstance()->executeS('SELECT id_customer FROM `'._DB_PREFIX_.'customer_group` WHERE id_group IN (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE "%MYGROUP%" AND id_lang=5)');

Let's give a try with this one and see what will be result..

Edited October 15, 2020 by Rachit Agarwal (see edit history)

[ThibaultWUN]

  On 10/15/2020 at 8:50 AM, EvaF said:

I am not sure, if you write correct 'mygroup'

 in the case of group:  user_%companyName%  

thus in specific case you want to find f.e. user_AAACompany

don't  forget that in mysql  underscore character "_" is considered as wildcard  - i.e. you should write

LIKE 'user\_AAACompany'

and result query should look like:

SELECT id_customer FROM ps_customer_group WHERE id_group IN (SELECT id_group FROM ps_group_lang WHERE name LIKE 'user\_AAACompany' AND id_lang=5)

 

Expand  

Yes ! I forgot about the wildcard ! It is much better now !