We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Storing multiple, not always necessary attributes in one row - EAV implementation?

Hi, is there any good EAV implementation for Phalcon? Making one by setting manually relationships for every single attribute would be very inelegant and dirty job.

What I'm trying to do is a mysql table for storing notifications and data for them. Let's say that I've got a few types of notifications and every type needs a few different attributes. I can see only three way of solving it:

  1. add all attributes to same table and set nulls for unused ones (in my opinion inelegant, makes table large)
  2. store all non universal attributes in json attribute (come on, no filtering in selects? inacceptable)
  3. EAV - long phql code or hand made relationships for every attribute (both are kinda dirty)

I'm out of ideas, can anyone think of a better idea?

1


145.0k
edited Jun '16

Well what just about:

table notification
id, type etc
table attribute
id, name
table attributenotification
notification_id, attirubte_id, value

Why you need it in one row ? What's a problem with this ?



4.9k
in reply to Wojciech Ślawski
edited Jun '16

Well what just about:

table notification
id, type etc
table attribute
id, name
table attributenotification
notification_id, attirubte_id, value

Why you need it in one row ? What's a problem with this ?

And how would you define the relationships in models and how would you use such model? remember that there is more than one type of attributes



79.0k

EAV is specific. There's up to developer to implement such business logic. I don't think ther'e an easy access in Phalcon / ORM.

Entity–attribute–value model (EAV) is a data model to encode, in a space-efficient manner, entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest. Such entities correspond to the mathematical notion of a sparse matrix. EAV is also known as object–attribute–value model, vertical database model and open schema.

https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model



145.0k
edited Jun '16

What you mean by type of attributes ? You mean that you want attributes with same type in one property and another type in another property in model ? If yes then:

table notification
id, type etc
table attribute
id, name, type_id
table attribute_type
id, name
table attributenotification
notification_id, attirubte_id, value

But the problem would be with defining relationships. You should need there joins etc, tbh it can be done but it would be nasty. In relations you can pass in options params option as far as i know which will be passed to ::find() method, it's somehow possible to make joins, even in ::find() method but this will be nasty.



79.0k

(example) Catalog EAV Attribute Table 

 Column     Type    Comment
attribute_id    smallint(5) unsigned    Attribute ID
frontend_input_renderer varchar(255) NULL   Frontend Input Renderer
is_global   smallint(5) unsigned [1]    Is Global
is_visible  smallint(5) unsigned [1]    Is Visible
is_searchable   smallint(5) unsigned [0]    Is Searchable
is_filterable   smallint(5) unsigned [0]    Is Filterable
is_comparable   smallint(5) unsigned [0]    Is Comparable
is_visible_on_front smallint(5) unsigned [0]    Is Visible On Front
is_html_allowed_on_front    smallint(5) unsigned [0]    Is HTML Allowed On Front
is_used_for_price_rules smallint(5) unsigned [0]    Is Used For Price Rules
is_filterable_in_search smallint(5) unsigned [0]    Is Filterable In Search
used_in_product_listing smallint(5) unsigned [0]    Is Used In Product Listing
used_for_sort_by    smallint(5) unsigned [0]    Is Used For Sorting
apply_to    varchar(255) NULL   Apply To
is_visible_in_advanced_search   smallint(5) unsigned [0]    Is Visible In Advanced Search
position    int(11) [0] Position
is_wysiwyg_enabled  smallint(5) unsigned [0]    Is WYSIWYG Enabled
is_used_for_promo_rules smallint(5) unsigned [0]    Is Used For Promo Rules
is_required_in_admin_store  smallint(5) unsigned [0]    Is Required In Admin Store
is_used_in_grid smallint(5) unsigned [0]    Is Used in Grid
is_visible_in_grid  smallint(5) unsigned [0]    Is Visible in Grid
is_filterable_in_grid   smallint(5) unsigned [0]    Is Filterable in Grid
search_weight   float [1]   Search Weight
additional_data text NULL   Additional swatch attributes data


145.0k

Oh he means this by type ? Then i guess only one way is to store all values in some column like text :/ And then in afterFetch cast it to type which you want or something, don't really know how to solve it another way.