Wednesday 5 September 2018

Complex SQL query does not give the expected result

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?

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

0 comments:

Post a Comment