Joining two tables in laravel - how to have second table data as a property of result

Question

I have two tables A and B.

Table A:

no  name  type
1   shoe   1
2   shirt  2
3   book   3

Table B:

type color   size
1    red     big
2    yellow  small
3    blue    medium

When I query where A.no === 1 and A.type === 1, I want to get data like:

{
  no: 1,
  name: 'shoe',
  type: 1,
  info: {
    color: 'red',
    size: 'big'
  },
}

I tried something like this:

select a.*, b.* from stores a, types b where a.type = 1 and a.type = b.id

and it returns only plain object, I want to get nested data like the above. I think it can be done using join and doing any other query tricks.

Here's the sql fiddle link I prepared for you. http://sqlfiddle.com/#!9/3ad910/2

Thanks in advance.

Answer 1

Model TableA:

public function info()
{
    return $this->hasOne(TableB::class, 'type', 'type');
}

Model TableB:

public function tableA()
{
    return $this->belongsTo(TableA::class, 'type', 'type');
}

The Query:

TableA::with('info')->where(['type' => 1, 'no' => 1])->get();

Answer 2

So the query that you have (although better written using post-1992 query syntax) is all that you need. The rest of the problem is a simple case of rearranging the resulting array. I don't know eloquent/laravel, and I'm embarrassingly bad at rearranging arrays, but here's an example of the kind of thing I mean using plain old php (perhaps someone will be kind enough to write a more apt array transformation)...

<?php

/*
DROP TABLE IF EXISTS table_a;

CREATE TABLE table_a
(no SERIAL PRIMARY KEY
,name  VARCHAR(12) UNIQUE
,type INT NOT NULL
);

INSERT INTO table_a VALUES
(1,'shoe',1),
(2,'shirt',2),
(3,'book',3);

DROP TABLE IF EXISTS table_b;

CREATE TABLE table_b
(type SERIAL PRIMARY KEY
,color VARCHAR(12) NOT NULL
,size VARCHAR(12) NOT NULL
);

INSERT INTO table_b VALUES
(1,'red','big'),
(2,'yellow','small'),
(3,'blue','medium');

SELECT a.no
     , a.name
     , b.*
  FROM table_a a
  JOIN table_b b
    ON b.type = a.type
 ORDER
    BY a.no;
+----+-------+------+--------+--------+
| no | name  | type | color  | size   |
+----+-------+------+--------+--------+
|  1 | shoe  |    1 | red    | big    |
|  2 | shirt |    2 | yellow | small  |
|  3 | book  |    3 | blue   | medium |
+----+-------+------+--------+--------+
*/

require('path/to/connection/stateme.nts');

$query = "
SELECT a.no
     , a.name
     , b.type
     , b.color
     , b.size
  FROM table_a a
  JOIN table_b b
    ON b.type = a.type
 WHERE a.no = 1
   AND a.type = 1
 ORDER
    BY a.no;
";

$result = mysqli_query($db,$query) or die(mysqli_error());

$old_array = array();

while($row = mysqli_fetch_assoc($result)){

$old_array[] = $row;

}

$new_array = array();

foreach ($old_array as $row) {
   $new_array[]['name'] = $row['name'];
   $new_array[$row['no']]['info']['color'] = $row['color'];
   $new_array[$row['no']]['info']['size'] = $row['size'];
}

$new_array = array_values($new_array); // reindex


print_r($new_array);

?>

Outputs:

Array
(
    [0] => Array
        (
            [name] => shoe
        )

    [1] => Array
        (
            [info] => Array
                (
                    [color] => red
                    [size] => big
                )

        )

)

or, json_encoded...

[{"name":"shoe"},{"info":{"color":"red","size":"big"}}]