Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I'm building inventory app, which will primary be used for managing information about tools in company and how many and which tools are available or given for use to someone. Tools or Items, should be separated in categories, and each category should have set of properties i.e. attributes which can be defined for specific Item but not have to.
I built Eloquent models and relations between them:
class Category extends Model
{
public function items() {
return $this->hasMany(Item::class);
}
public function categoryProperties() {
return $this->hasMany(CategoryProperty::class);
}
}
class Item extends Model
{
public function category(){
return $this->belongsTo(Category::class);
}
public function itemProperties(){
$this->hasMany(ItemProperty::class);
}
}
class ItemProperty extends Model
{
public function item(){
return $this->belongsTo(Item::class);
}
public function categoryProperty(){
return $this->belongsTo(CategoryProperty::class);
}
}
class CategoryProperty extends Model
{
public function category(){
return $this->belongsTo(Category::class);
}
public function itemProperties(){
return $this->hasMany( ItemProperty::class, 'property_id');
}
}
My database tables look like this:
What is best way to retrieve data about Items from specific Category with all defined properties for them, suitable for showing in table, assuming that some Items don't have entries in ItemProperties table for some CategoryProperties of Category to which they belong?
I hope that my question is clear enough.
I can think of 2 possibilities.
Your table rows are the Items with properties and category properties in columns.
for this you have to start from the items: new Item()->with('categories', 'itemProperties')
Go trough the categories, and make detail rows, for the items that belong to the category.
Category()->with('items', 'categoryProperty')
I tried both possibilities, but i don't get what I need. I will give one simplified example of what I want to acheive:
in table category i have entry
- cats
in table category_properties i have entries
- color of eyes
- length of tail
- shape of ears
In table items i have entry
- Garfield
and in item_properties I have only one entry
- green
related to Garfield in items table and color of eyes in category_properties.
I need [ Garfield => [ 'color of eyes' => 'green', 'length of tail' => ' ', 'shape of ears' => ' ']],
instead of [Garfield => [ 'color of eyes' => 'green']]
I'm having trouble to even explain where I'm stuck.
Why don't you just query what you need and be done with it.
That is last line of defence. I thought that there is much more elegant and already developed way to make it.
What do you think eloquent is? It's an active record shortcut that converts to normal SQL at runtime. It in itself is not a language like C++ is.
I use it sometimes myself for simple things but as a query gets a little more complex I use the DB facade or getPdo ().
It would probably just be something like
$data = Category::with(['categoryProperties', 'item.itemProperties'])->get();
for each category, get the category properties, along with items and itemProperties for each category.
Then in the view, you'd just check if there was data in each class property using count()
{{ $catetory->name }}
@if ($category->categoryProperties)
{{ $category->cagetoryProperties->name }}
@endif
@if ($category->item->count())
@foreach ($category->item as $item)
{{ $item->name }}
@if ($item->itemProperties->count())
@foreach($item->itemProperties as $property)
{{ $property->name }}
@endforeach
@endif
@endforeach
@endif
There's lots of ways to do this really.
@jlrdw I understand. I used to work with Hibernate ORM in Java for a while, and guided by that experience i hoped that Eloquent is something similar, but it's different on so many levels.
I came from Java, but been many years since that. I have also used Doctrine ORM for a while. Doctrine and Hibernate are similar.
But to me as a query gets a little more complex and you need to start using the:
I halt right there and use getPdo(). Which eloquent also uses.
I did a guide and here are some references. But choice is yours, I am just sharing information:
https://laracasts.com/discuss/channels/guides/getpdo-usage
https://laracasts.com/discuss/channels/eloquent/writing-all-queries-directly-vs-model-relations
https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder
https://laracasts.com/discuss/channels/laravel/coverting-ms-access-queries-to-laravel-query-builder
https://laracasts.com/discuss/channels/general-discussion/sql-injection-2
I never used hibernate, I did EJB's, servlets, jsp at a trucking company. Logistics software. Semi retired now, just do smaller PHP apps, most recent an accounting app, even had inline edit. No packages used. My JS code, took a while.
I have learned and used eloquent, but found it's not so great as data grows. Picture
@foreach ($category->item as $item)
{{ $item->name }}
@if ($item->itemProperties->count())
@foreach($item->itemProperties as $property)
{{ $property->name }}
@endforeach
@endif
@endforeach
if one of the inner foreach's had thousands of results. I.e., a large A/R run.
Just saying...
@jlrdw Database designer is MySQL workbench, very nice crossplatform tool :) https://www.mysql.com/products/workbench/
Many times I use designer in Microsoft Access to build up a query then copy and paste and adjust accordingly for MySQL.
AT trucking company I would dump data to local, and via odbc, use access for complex reports. This was Intranet.
Please or to participate in this conversation.