User Generated Tables (ala filemaker), sorting/filtering/pagination

[fivestringsurf]

I’m writing a small application that allows me/users to add their own inputs so they can store their own data.  It’s kind of like, a user defined database.  Think of a super simple “filemaker” kind of application.    The way I’ve set this up is like this:

Table: item
fields: item_id, user_id

Table item_attribute
Fields: item_attribute_id, item_id, name, value

After my query I end up with stuff like this:

$data = array(
            0=>array(
                        '123'=>array(
                                    'name'=>'Year',
                                    'value'=>'1985'
                        ),
                        '7'=>array(
                                    'name'=>'Title',
                                    'value'=>'Title For 1985'
                        ),
                        '25'=>array(
                                    'name'=>'Length',
                                    'value'=>'60'
                        )
            ),
            1=>array(
                        '123'=>array(
                                    'name'=>'Year',
                                    'value'=>'1990'
                        ),
                        '7'=>array(
                                    'name'=>'Title',
                                    'value'=>'Title For 1990'
                        ),
                        '25'=>array(
                                    'name'=>'Length',
                                    'value'=>'44'
                        )
            ),
            2=>array(
                        '123'=>array(
                                    'name'=>'Year',
                                    'value'=>'1965'
                        ),
                        '7'=>array(
                                    'name'=>'Title',
                                    'value'=>'Title For 1965'
                        ),
                        '25'=>array(
                                    'name'=>'Length',
                                    'value'=>'122'
                        )
            )
);

This seems to work great as there is no limit to what a user can add - it's working like I would like it to...

 

But now that I’m trying to add sorting/filtering/pagination to results things have gotten extremely difficult on the back end.

 

The only thing I can think of is to pull all the data and then use php algorithms to sort/filter/paginate before sending over to web page.  I can see this getting slow if I eventually have to pull 1000s of records.

 

 

Does anyone have advice in these matters? 

Should I rethink my db design? 

Can the results be used to create a temporary table and then sort on it?  (don'n know how or if this is even possible)

I need some help

 

Thanks.

Edited November 16, 2014 by fivestringsurf

[Barand]

Databases were designed for filtering and sorting, it's what they do. Use the power of the query.

[fivestringsurf]

@Barand, sure I can do that with regular arrangements when I'm sorting on a column/field, but with my db the way it is now; there is never a field to sort on.  For example, look at my sample array.  If I wanted to sort based on "Year" there is not a field labled "Year" in the database.  Instead years are stored in the table: item_attribute under the field:name="Year" and field:value="1985"

So a row would look like:

item_attribute_id | item_id | name  | value
12 |  3  |  Year  |  1985

Upon further investigation maybe I can I use the ORDER BY FIELD() clause/function ?

 

And how can I retrieve specific number of rows for pagination if I'm using many rows to create "one item"?

 

Any examples/suggestions would be helpful.

 

Thanks.

 

 

 

 

[Barand]

I have come across this model a couple of times. My test model is attached. Basically, products belong to categories and each category would have its own set of attributes (for example a camera will have a different set of attributes from a bed). Also the attributes have a start date/end date to allow, for example, price changes over time.

 

The technique I used was to to create a table subquery to join the required attribute values into a single row which could be sorted, filtered etc.

 

To make life easier I created a function which took an array of the required attribute ids and generated the required subquery.

 

See this thread reply

http://forums.phpfreaks.com/topic/277085-the-eav-model-or-something-better/?do=findComment&comment=1431597

[fivestringsurf]

@Barand

Funny, I didn't even know this was a "model"...I was just doing what made sense to me    But boy what a nightmare to filter through stuff.

I figured out I can sort like this:

ORDER BY field( attribute.name, 'Year' ) DESC , value ASC

But I can't limit the query in a useful way for pagination and I can't limit the scope of the search as in  WHERE 'year' = 2014 ... because the year only exists as one attribute of many.
It's such a headache that I'm thinking of a redesign maybe.
have two tables:

table: item
fields: item_id, attr1, attr2, attr3 ...

table: item_meta
fields: item_meta_id, item_id, data_type, name

This way i can perform normal operations on a single table and refer to the meta table for the user generated names, datatypes, etc.

I would just have to limit the amount of attributes a user could define.  so table: item would have up to say attr50  (50 fields)

 

I wonder if this idea is some type of "model" too?

Edited November 18, 2014 by fivestringsurf

[Barand]

 

 

table: item
fields: item_id, attr1, attr2, attr3, ... , attr50

 

 

 

... attributes a user could define

 

Good luck!