Complex sql query not giving expected result

Question

I have constructed the following query:

SELECT p.id person_id, p.name person_name, p.dob person_dob,
        a.attribute, pa.value, t.type person_type

       FROM      people               p
       LEFT JOIN person_attributes    pa     ON pa.person_id=p.id
       LEFT JOIN person_types         pt     ON pt.person_id=p.id
       LEFT JOIN attributes           a      ON pa.attribute_id=a.id
       LEFT JOIN types                t      ON pt.type_id=t.id
       WHERE p.id='$person_id'

Here's my db structure:

people(id,name,dob)
person_attributes(id,person_id,attribute_id,value)
attributes(id,attribute)
person_types(id,person_id,type)
types(id,type)

The result of the query is not giving me my expected results as it returns a few objects instead of one. I want to select person and all related data(attributes and types). I think I've messed up with those JOINS or something. I've tried reordering them etc.

Current result is:

Array
(
[0] => stdClass Object
    (
        [person_id] => 2
        [person_name] => Marta Smith
        [person_dob] => 1995-03-16
        [attribute] => size
        [value] => the_value
        [person_type] => type2
    )

[1] => stdClass Object
    (
        [person_id] => 2
        [person_name] => Marta Smith
        [person_dob] => 1995-03-16
        [attribute] => size
        [value] => the_value
        [person_type] => type1
    )

[2] => stdClass Object
    (
        [person_id] => 2
        [person_name] => Marta Smith
        [person_dob] => 1995-03-16
        [attribute] => weight
        [value] => the_value
        [person_type] => type2
    )

[3] => stdClass Object
    (
        [person_id] => 2
        [person_name] => Marta Smith
        [person_dob] => 1995-03-16
        [attribute] => weight
        [value] => the_value
        [person_type] => type1
    )

)

Expected result:

[0] => stdClass Object
    (
        [person_id] => 2
        [person_name] => Marta Smith
        [person_dob] => 1995-03-16
        [attributes] => // array of all attributes with values here
        [types] => // array of all person types here
    )

Are there any changes that can be made to the query to format it as expected? or the only way to do it is to use PHP, loop through the result and create new object?

I think you can `group by` the `p.id` then `group_concat` the `a.attribute` and `t.type`. That will make a delimiter string, not an array for the `attributes` and `types` index. You could explode it to get an array but whatever delimiter you choose (make sure it is unique). — chris85, Mar 04 '17 at 21:55
This is a simple query, returning a straightforward and practical result. No modification required. — Strawberry, Mar 04 '17 at 22:37

score 0 · Answer 1 · answered Mar 04 '17 at 21:55

0

Well i guess the problem is in your SELECT fields part. It should be:

SELECT p.id AS person_id, p.name AS person_name, p.dob AS person_dob,
    a.attribute, pa.value, t.type AS person_type

answered Mar 04 '17 at 21:55

Eduardo Escobar

3,301
2
18
15

score 0 · Answer 2 · edited May 23 '17 at 11:53

That's just how SQL works - the problem you're having is that SQL is going to return you a two dimensional flattened table structure for what you're thinking of as a nested tree structure. In other words, your SQL result is something like this:

2   Marta Smith 1995-03-16  size    the_value   type1
2   Marta Smith 1995-03-16  weight  the_value   type1
2   Marta Smith 1995-03-16  weight  the_value   type2

And you need to come up with a way to aggregate/group that information. You have a few options:

Break your query up into multiple queries to get only the distinct sets from SQL that you're interested in. This is generally not preferable - it will add more round trips to the server and still require that you do some client side aggregation in php or javascript
Aggregate/group things in PHP or JavaScript - preferably broken out into a business logic layer that is reusable. That layer would iterate through your SQL results and translate them to your expected results.
Use some kind of XML functionality in MySQL with XSLT to transform it (XML output from MySQL). This isn't a great option with MySQL, but may be a better option with RDBMS that support XML or JSON output formats more fully (like SQL Server, Oracle, or DB2).
Turn some of your joins into subqueries and concatenate them, then use a string splitting function - but be careful about this one, in case your data could contain your delimiter unexpectedly (I'm not a big fan of this approach). See Concatenate many rows into a single text string? for an example - and basically take any of your 0/1 to many relationships and use a subquery instead of a LEFT JOIN

Complex sql query not giving expected result

2 Answers2