CodeIgniter GROUP_CONCAT and join

问题:

I am trying to figure a way to join these two tables together, which I was able to do, but if it found more than one value that matched, it showed everything from the product table again. Now I am trying to use the MySQL group_concat together to be able to list all of tName in one field in the array but I keep getting an error with MySQL:

Error Number: 1064

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 'FROM (sp_product) LEFT OUTER JOIN sp_product_type ON sp_product_type.`tCat' at line 2

SELECT sp_product.name, sp_product.price, sp_product.perm_name, sp_product.description, GROUP_CONCAT(product_type.tName SEPARATOR FROM (sp_product) LEFT OUTER JOIN sp_product_type ON sp_product_type.tCategory = sp_product.type WHERE perm_name = 'bacon'

$this->db->select('product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(product_type.tName SEPARATOR ',') as product_type.tName'); 
$this->db->from('product');
$this->db->where('perm_name', $this->uri->segment(2));
$this->db->join('product_type', 'product_type.tCategory = product.type', 'LEFT OUTER');
$query = $this->db->get(); 

Any ideas what I am doing wrong?

回答1:

Seems problem with improper quotes.

It should be GROUP_CONCAT(product_type.tName SEPARATOR ",")

Try below :

$this->db->select('product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(product_type.tName SEPARATOR ",") as product_type.tName'); 
    $this->db->from('product');
    $this->db->where('perm_name', $this->uri->segment(2));
    $this->db->join('product_type', 'product_type.tCategory = product.type', 'LEFT OUTER');
    $query = $this->db->get(); 

回答2:

You should use false as the second parameter in your select to skip escaping:

$this->db->select('GROUP_CONCAT(product_type.tName SEPARATOR ",") as product_type.tName', false);

回答3:

no need to mention separator as comma(,) in group concat, as by default it will take comma(,) only in group concat

回答4:

Take a look at the GROUP_CONCAT documentation:

Change this:

GROUP_CONCAT(product_type.tName SEPARATOR

To this:

GROUP_CONCAT(product_type.tName SEPARATOR ', ')

You're mixing the '

'product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(product_type.tName SEPARATOR ',') as product_type.tName');

Try this:

"product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(product_type.tName SEPARATOR ',') as product_type.tName");

回答5:

user319198 was off to a good start but his answer does not work. The working answer is as follows.

$this->db->select('product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(product_type.tName SEPARATOR) as tName'); 
    $this->db->from('product');
    $this->db->where('perm_name', $this->uri->segment(2));
    $this->db->join('product_type', 'product_type.tCategory = product.type', 'LEFT OUTER');
    $query = $this->db->get(); 

Notice I removed SEPARATOR "," and renamed the field in (as product_type.tName') to a field name that is not in the database. This has been tested and is working.

回答6:

For #Claremont, #user319198, #FredTheLover, #Mosty Mostacho Please keep in mind while developing any project using CodeIgniter that to select multiple data or using compound statements in the select query of the codeigniter you need to define the second parameter of the select query. Like

$this->db->select('(SELECT SUM(company.amount) FROM payments WHERE company.invoice_id=4') AS amount_paid', FALSE);

Because $this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

回答7:

I think it should be like this:

$this->db->select('product.name, product.price, product.perm_name, product.description')
   ->select(' GROUP_CONCAT(product_type.tName SEPARATOR ",") as product_type.tName', FALSE); 
$this->db->from('product');
$this->db->where('perm_name', $this->uri->segment(2));
$this->db->join('product_type', 'product_type.tCategory = product.type', 'LEFT OUTER');
$query = $this->db->get();

回答8:

try this ,assuming you use id to sort, use GROUP_BY instead of ORDER_BY and also use DISTINCT .. this will work

 $this->db->group_by("product.id","ASC");
$this->db->select('product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(DISTINCT product_type.tName SEPARATOR ",") as tName',FALSE); 
$this->db->from('product');
$this->db->where('perm_name', $this->uri->segment(2));
$this->db->join('product_type', 'product_type.tCategory = product.type', 'LEFT OUTER');
$query = $this->db->get();