CODEWITHSUNDEEP
mysqlsqldatabase
Vecta
Vecta

Reputation: 2350

Merging Many to Many Results

I'm using the following SELECT statement:

SELECT *
FROM prefix_site_tmplvars
LEFT JOIN prefix_site_tmplvar_contentvalues 
ON prefix_site_tmplvar_contentvalues.tmplvarid = prefix_site_tmplvars.id
LEFT JOIN prefix_site_content
ON prefix_site_tmplvar_contentvalues.contentid = prefix_site_content.id
WHERE prefix_site_tmplvar_contentvalues.value = "chocolate"

This is what I get back:

[id] => 2  
[name] => flavor  
[value] => chocolate

[id] => 2 
[name] => type 
[value] => cookie

This is the result I'd like to get:

[id] => 2
[flavor] => chocolate
[type] => cookie

Is there a way to combine my results so I don't have a bunch of rows referring to the same ID? If now, how should I handle this?

I'm using Modx and this is working with the Template Variable tables: http://wiki.modxcms.com/index.php/Template_Variable_Database_Tables

Upvotes: 0

Views: 57

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can do this with a group by:

SELECT id,
       max(case when name = 'flavor' then value end) as flavor,
       max(case when name = 'type' then value end) as type
FROM prefix_site_tmplvars LEFT JOIN
     prefix_site_tmplvar_contentvalues
     ON prefix_site_tmplvar_contentvalues.tmplvarid = prefix_site_tmplvars.id LEFT JOIN
        prefix_site_content ON prefix_site_tmplvar_contentvalues.contentid = prefix_site_content.id
WHERE prefix_site_tmplvar_contentvalues.value = "chocolate" 
group by id

Upvotes: 0

McGarnagle
McGarnagle

Reputation: 102743

You can just use case statements:

SELECT 
    id,
    MAX( CASE WHEN name = 'flavor' THEN value ELSE NULL END ) AS flavor,
    MAX( CASE WHEN name = 'type' THEN value ELSE NULL END ) AS type
FROM prefix_site_tmplvars
    LEFT JOIN prefix_site_tmplvar_contentvalues 
    ON prefix_site_tmplvar_contentvalues.tmplvarid = prefix_site_tmplvars.id
    LEFT JOIN prefix_site_content
    ON prefix_site_tmplvar_contentvalues.contentid = prefix_site_content.id
WHERE prefix_site_tmplvar_contentvalues.value = "chocolate"
GROUP BY id

Of course, this approach only works if you know ahead of time what keys you want to select; but it seems like in this case you do know (flavor + type).

Upvotes: 1

LSerni
LSerni

Reputation: 57388

Problem is, you might have

{ "id": 2, "flavor": "chocolate", "type": "cookie" }

and another row with

{ "id": 3, "flavor": "vanilla", "calories": "375" }

...I don't think there's an easy way to solve the problem in MySQL; you'd need to decide what keys to look for.

On the other hand you can collapse the rows in PHP:

while($tuple = ...fetch tuple from mysql cursor...)
{
    list ($id, $name, $value) = $tuple;
    if (!isset($objs[$id]))
    {
        // You might want to add also "'id' => $id" to the array definition
        $objs[$id]= array ($name => $value);
    }
    else
    {
        $obj = $objs[$id];
        $obj[$name] = $value;
        $objs[$id] = $obj;
    }
}

Now $objs contains the desired data; you still need to get it back into Modx, though.

Upvotes: 0

Related Questions