1

Having difficulty, even after reading the docs, to get the 'property name' from the related 'properties' table while querying for a product from the 'products' table with the pivot 'product_properties'.

My Models: Models\Product::class, Models\Property::class, Models\ProductProperty::class;

If I run the query:

$product = new App\Models\Product();
$product->where('id', 1)->with('properties')->get();

I get the full product object along with the properties, and this is working good, but what I can't figure out is how to get the property name from the properties table.

In summary, I need to display the property name from the properties table that is related with the product_properties table. Ex: 130mm width

What I need to achieve is something like the following:

Illuminate\Database\Eloquent\Collection {#4400
     all: [
       App\Models\Product {
         id: 1,
         name: "Chrystal Block",
         details: "Ships in an exclusive branded box.",
         price: 50.00,
         has_shipment: 1,
         created_at: "2019-05-10 02:15:22",
         updated_at: "2019-05-10 02:15:22",
         deleted_at: null,
         properties: Illuminate\Database\Eloquent\Collection {
           all: [
             App\Models\ProductProperty {
               product_id: 4,
               property_id: 1,
               value: "130",
               unit: "mm",
               created_at: "2019-05-11 23:09:35",
               updated_at: "2019-05-11 23:09:35",
               deleted_at: null,

               // ***** This is what I'm looking for (hardcoded here *****
               property: Illuminate\Database\Eloquent\Collection {
                all: [
                    App\Models\Property {        
                        id: 1,
                        name: "width",
                        created_at: "2019-05-11 23:09:35",
                        updated_at: "2019-05-11 23:09:35",        
                    ]
                },

             },
             App\Models\ProductProperty {
               product_id: 4,
               property_id: 2,
               value: "17",
               unit: "mm",
               created_at: "2019-05-11 23:09:35",
               updated_at: "2019-05-11 23:09:35",
               deleted_at: null,
             },
             App\Models\ProductProperty {
               product_id: 4,
               property_id: 3,
               value: "60",
               unit: "mm",
               created_at: "2019-05-11 23:09:35",
               updated_at: "2019-05-11 23:09:35",
               deleted_at: null,
             },
             App\Models\ProductProperty {
               product_id: 4,
               property_id: 4,
               value: "2.78",
               unit: "kg",
               created_at: "2019-05-11 23:09:35",
               updated_at: "2019-05-11 23:09:35",
               deleted_at: null,
             },
           ],
         },
       },
     ],
   }

Thanks in advance for any help in this Eloquent query.

My tables are the following with sample data:

Table 'products'

+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name         | varchar(191)        | NO   |     | NULL    |                |
| details      | text                | NO   |     | NULL    |                |
| price        | double(8,2)         | NO   |     | NULL    |                |
| has_shipment | tinyint(1)          | NO   |     | NULL    |                |
| created_at   | timestamp           | YES  |     | NULL    |                |
| updated_at   | timestamp           | YES  |     | NULL    |                |
| deleted_at   | timestamp           | YES  |     | NULL    |                |
+--------------+---------------------+------+-----+---------+----------------+

Values: 

(1, 'Chrystal Block', 'Ships in an exclusive branded box.', 50.00, 1, '2019-05-10 02:15:22', '2019-05-10 02:15:22', NULL);

Table 'properties'

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name       | varchar(191)        | NO   |     | NULL    |                |
| created_at | timestamp           | YES  |     | NULL    |                |
| updated_at | timestamp           | YES  |     | NULL    |                |
| deleted_at | timestamp           | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

Values:

(1, 'width', '2019-05-11 22:53:45', '2019-05-11 22:53:45', NULL),
(2, 'height', '2019-05-11 22:53:45', '2019-05-11 22:53:45', NULL),
(3, 'length', '2019-05-11 22:53:45', '2019-05-11 22:53:45', NULL),
(4, 'weight', '2019-05-11 22:53:45', '2019-05-11 22:53:45', NULL);

Table 'product_properties'

+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| product_id  | int(10) unsigned    | NO   | MUL | NULL    |       |
| property_id | int(10) unsigned    | NO   | MUL | NULL    |       |
| value       | varchar(191)        | NO   |     | NULL    |       |
| unit        | varchar(191)        | NO   |     | NULL    |       |
| created_at  | timestamp           | YES  |     | NULL    |       |
| updated_at  | timestamp           | YES  |     | NULL    |       |
| deleted_at  | timestamp           | YES  |     | NULL    |       |
+-------------+---------------------+------+-----+---------+-------+

Values:

(1, 1, '130', 'mm', '2019-05-11 23:09:35', '2019-05-11 23:09:35', NULL),
(1, 2, '17', 'mm', '2019-05-11 23:09:35', '2019-05-11 23:09:35', NULL),
(1, 3, '60', 'mm', '2019-05-11 23:09:35', '2019-05-11 23:09:35', NULL),
(1, 4, '2.78', 'kg', '2019-05-11 23:09:35', '2019-05-11 23:09:35', NULL);
McRui
  • 1,879
  • 3
  • 20
  • 31
  • foreach($data as $D) { foreach($D->ProductProperty as $any) { $any->name; } // or get frist in array $D->ProductProperty->frist()->name ; // if you want convert array to text $D->ProductProperty->implode('name ' , ',') } – xpredo May 11 '19 at 23:45
  • That doesn't look like what I'm looking for. I want to get the **property name** already in the `Collection` in `relations`. – McRui May 11 '19 at 23:55
  • If you get a Collection of multiple properties, `$product->property` won't work (assuming a product has many properties). So `$product->properties[0]` work, right? What you can do is maybe use pluck to get only the property names if you don't want other values. It's not really clear what you want to achieve – senty May 12 '19 at 00:08
  • @senty I've updated the question with a sample of the collection result with "hardcoded" result of what I want to achieve. Thanks – McRui May 12 '19 at 00:24
  • 1
    Can you try `$product->where('id', 1)->with('properties.property')->get();`. ProductProperties should have Property relationship in its class as `$this->belongsTo(Property::class);` [Check this answer](https://stackoverflow.com/a/18963625/4705339) for more clear example – senty May 12 '19 at 00:28
  • @senty thanks so much. It works like charm! Two things, I was missing the `->with('properties.property')` part and the relationship in `ProductProperties` was incorrect. With your answer – the `$this->hasOne(Property::class);` works as I was looking for. Thanks again! – McRui May 12 '19 at 00:35
  • 1
    Awesome! happy coding :) (Just a heads up, maybe `belongsTo` fits better than `hasOne` in your scenario, because Product Property actually belongsTo Property) – senty May 12 '19 at 00:36
  • The result is the same @senty but thanks for pointing that out. :) – McRui May 12 '19 at 00:40
  • 1
    @McRui They seems like they behave similarly but they don't 100%. Take a look at [this](https://stackoverflow.com/a/37583651/4705339), it may save you from headache in a few days :p – senty May 12 '19 at 00:42
  • I changed to `$this->belongsTo(Property::class, 'property_id', 'id'); as suggested and yes, it makes a difference as per the question/answer you linked.` Thanks :) – McRui May 12 '19 at 00:45

1 Answers1

5

For getting relationship of relationship (Nested Relationship) you can try

$product->where('id', 1)->with('properties.property')->get();

I really love the explanation in this answer for its clarity.

senty
  • 12,385
  • 28
  • 130
  • 260