Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Help designing relating models
  • Hello. I'm new to ORM and can use some help designing my database for it. 

    Right now, I am building a site where users can create lists and add items to them. Other users can also read and write to these lists. Originally when reading through the docs, I was thinking of setting up the database with the following tables (I'll list the relavant fields with them):

    users: (id), users_lists: (user id, list id), lists: (id). 

    With this set up, I was thinking I can just use the many_has_many relations to achieve gathering lists that the user owns or subscribes to. However, I need to introduce at least one other table, which would be to hold the list items. The items are also categorized, so I may even make separate tables per type of item. 

    Doing some further reading, the docs said if you are planning on going many relations deep, you should use nested relationships. 

    My question is, if I use nested relationships, will I be able obtain, lets say, an array of the lists users have, and the lists users have, with all their items AND if I use the nested relationship method, do I need the in-between tables to make it work (users_lists, lists_items, etc).  

    Also, as a side note, when making those in-between tables, are they models? Or can I just make them with sql?  
  • HarroHarro
    Accepted Answer
    Ideally, you would use migrations to maintain your database schema. It forces you to work in a structured way, and also allows you to easily recreate your database (on the same or another server). In your migrations you use DBUtil calls.

    As to ORM: if you use a many_many relation and your relationship (or junction or through) table does not contain any other properties, you don't need to create a model for it. On the many_many relation just define the through table that contains both foreign keys.

    If your relationship table does contain other properties, or perhaps relations to other tables, you do have to create a model for it. You then define belong_to relations on this model going to each side of the relation, and instead of a many_many you define the relation as has_many.

    You can still use the many_many relation to directly access the other end of the relation, the ORM sees them as two different relations.
  • Ah, I see, so for the through tables, they do not have to be models unless it contains more than the relationship ids from both models. 

    But I still don't understand if say, I need to get all the lists and all their items for a certain user, if I can still use many_many (it is more than 1 relationship deep), or do I use the nested structure. And if it is the nested structure, do I still need the through tables? 

    It would be users->lists->items. The lists table would have more than just an id, but also include name, category, type, etc.
  • HarroHarro
    Accepted Answer

    What do you mean by "nested" structure? Designing your database is a normal normalisation process.

    If you have users, and users have zero or more lists, and every list has zero on more items, then define those tables, and the relations between them.

    The ORM doesn't care:

    $all = Model_User::query()->related('lists')->related('lists.items')->get();

    Job done.
  • That makes sense thank you. The nested structure I was referring to was this:
  • Ah, ok. Then we're saying the same thing. ;-)
  • Ok, so to further expand on this issue:

    So as of right now, users make lists of accounts across their various social networks. I am trying to think of a way to design my database in the most efficient matter, but I am having trouble visualizing it.

    RIght now, I'm thinking, I would have the list table, with all the lists created by users. Then some sort of inbetween table that would relate to the respected service, and user id for that service. 

    For example, I would want to get all the user's in list 1. Let's say it contains accounts from facebook, twitter, and tumblr. I want to somehow store those accounts in their own tables for each service. How would I go about designing this?
  • I would say:

    * List has_many Listuser
    * Listuser  belongs_to List
    * Service has_many Listuser
    * Listuser  belongs_to Service

    Where Listuser is the junction table (model) between List and Service, and contains user_id (and maybe other data related to the relation).

    This will allow you to answer questions like
    - give me all users in list 1, with their service information
    - give me all facebook accounts, and the lists they are defined in

    The service table could then also contain details about how you connect to that service. I've done something similar where I store callbacks in the table, pointing to a class that does stuff like login, logout, statusinfo, etc. These classes have a unified API, so if tomorrow I want to introduce service "X", I just make a class for it, and add a row with callbacks to the Service table.
  • Ideally though, I would have a table for each service and each user would get saved in their respective service to keep things organized. I am really only looking to save the user id and service type, so when I retrieve the id, I know what service it came from so I can make relavant api calls with it. Also, users in the service tables could belong to multiple lists, so I would like to think of a way to keep only one entry in the service table per user to prevent duplicates, but have each list it belongs to point to it.

    For example, 

    user 1 from twitter, would be saved once in the twitter table, but in list user could belong to list 1, 2, and 3. User 1 would only be in the twitter table once though, instead of 3 times, once for each list. 
  • From a database design and normalisation poinf of view, designing it like that is an absolute nightmare, and will require you do code up everything manually. It will also mean that if you introduce a new service, you can change all the code you have created to deal with this setup.

    If this is what you want, by all means go ahead. But it won't be simple, and it won't be organized.

    If you're concerned that a specific user for a specific service is stored more then once, you can stick an extra table in there, that stores the user information.

    So you'll get:
    * List has_many Listuser
    * Listuser  belongs_to List
    * User has_many Listuser
    * Listuser  belongs_to User
    * Service has_many User
    * User belongs_to Service
  • What if I did something similar to your first answer by having three tables. list, lists_items, and item. With this, use a many_many relationship, and use lists_items as the through table. That way, users can belong to many lists, and lists can have many users? The only thing I worry about is how big the items table will get in the future, and how that will perfect performance when going to retrieve items. That is originally why I wanted to have some sort of organization for the users into separate tables. 

    If I did do that, would I be able to prevent duplicates in the items table? have user 1 in the items table, but have it belong to multiple lists, in the lists_items table. Such as,

    list_id  |  item_id
    1         | 1
    4         | 1
  • That is exactly like my last example, what I called "Listuser".

    Size shouldn't really be an issue, records are retrieved on primary key, which should always be indexed. It will definitely more straining in the database engine if you have to run 4 queries on 4 different tables then run one on a larger table.
  • Yes, you're right, I see that now. Sorry for the confusion. However, are you able to update the thru table to point to an existing entry to prevent duplicates with many_many? So if user 1 is in list one, and someone adds user 1 to list 2, I can just have the thru table point to user 1's id, since it's already in the items table, or do I have to use has_many, belongs_to to do that?

    Also, if that is the case, would I have to change the relationship for users and lists from many_many to has_many belongs_to, if other users can "subscribe" to already made lists? Thank you for your help and patience! haha.
  • That is exactly the point of such a table. It links one record at the left of the relation to one record at the right of the relation, so both left and right remain unique.

    So you'll get something like
    list_id | user_id
    1 | 1
    1 | 2
    2 | 1
    2 | 3
    And so on. The ORM will deal with everything once you have your models defined, so you can do things like
    // assign user 123 to existing list 1:
    $list = Model_List::find(1, 'related' => array('users'));
    $list->users[] = Model_User::find(123);

  • Right, so is that example many_many, or has_many / belongs_to?
  • HarroHarro
    Accepted Answer
    many-many, It's the only relation type that uses a relationship table. All other relation types have the FK in on of the tables.
  • Ok, so right now, I have a user model, list model, and item model. In the user model, I have many_many to list, and in the list model, I have many_many to item.When I make a new list, it works fine. I save the list and it saves the info in the thru table (users_lists). 

    However, when I try to update my list with items (with the near same strategy as making lists), I get an error. 

    ErrorException [ Error ]: [] operator not supported for strings

    This is thrown at the line $list->items[] = $item; I know this is an error for a string that you are calling as an array, but it shouldn't be happening. I var_dumped my $list just to make sure the object is actually there, and it is.  

  • HarroHarro
    Accepted Answer
    The error message seams pretty clear.

    $list->items is a string, and not an array. So you have to figure out where it got set or reset as a string. You don't have a column in your lists tables named "items"? You can't have a column and a relation with the same name...
  • That is exactly what it is and I didn't realize it. Thank you for pointing that out. I'll change it later.

    As for saving multiple items at once, is there a more efficient way to do this besides creating and saving each item model in a foreach loop?
  • HarroHarro
    Accepted Answer
    The ORM cascades save's by default, so if you save a parent, it will also save all children (if they are new of have changed values).
  • Ok, so thanks to your help, I have all the items being saved. 

    One more issue though. I send the data to the function via ajax. After the function is done, I want to redirect back to the main dashboard page. I do a Response::redirect('dashboard');. However, it doesn't redirect, but sends back the the html of that page to the ajax success function. I can see it via console.log(data); Why won't the page just redirect?

    Also, if I wanted to get all the lists a user has, could I use...

    $all = Model_User::query()->related('lists')->get();

  • I don't really get this. You're sending data to the client (for display I assume), and once that's done you want to redirect to another page? So the user can't see the data that was requested?

    That query will return all users, with their related lists.
  • Sorry, let me clarify. 

    This function I'm referring to is the one that saves the items in the user's list. I do this by collecting the data and sending it with ajax POST. After the data is sent, and done being entered into the db, I want to redirect the user back to the dashboard page. However, the redirect returns the html of the dashboard page which can be seen via console.log in the ajax success function, when I want it to actually redirect the page. 
  • HarroHarro
    Accepted Answer
    It's an ajax call, so all data the request produces is send back as a reponse to the call.

    Redirects works by adding a HTTP Location header, which is never going to work since your browser didn't request anything, it will never see a response.

    Instead, your ajax method should respond with a HTTP status code (200 for success, another for errors) that your javascript can check. You can send the URL to redirect to back as part of the 200 response, and then your javascript can put that out and do the redirect client-side.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion