RedBean multi table join

Question

I'm using RedBean to create a time sheet application.

Here is the (simplified) DB structure:

Owner

• id
• name
• email

Project

• id
• description
• owner_id

Task

• id
• description
• project_id

Log

• id
• start
• end
• description
• task_id

The table structure is far more complicated than above but that should be enough to demonstrate my issue.

There are hundreds of log items in a task and hundreds of tasks in a project.

So if I wanted to get all log items for a specific owner and get the associated tasks and projects along too how would I achieve that?

To use an SQL query to get the data is straight forward enough, I can choose what data I want etc but if I modify the data I need to create beans from it. As some of the tables have similar column names RedBean wont (or doesn't seem to magically) convert the data into beans.

So I guess my question is how do you construct a RedBean query, get, getAll, fetch, load etc which will convert data that it uses from each join into a bean?

The closest thing I can find is the traverse method which means Id recursively go into each bean to find its' children but this means returning the whole result set and processing it.

If the fastest way is just to run the SQL query and iterate though the data and create beans from it no problem. I'm doing that already, I just wanted a slightly more abstracted way to do it and would consider that RedBean might offer that functionality.

Answer 1

Just to give you an idea you can also use mySQL joins to properly get what you need using R::getAll(); e.g.

$result = getAll("SELECT L.* FROM logs L, tasks T, projects P, owner O 
    WHERE 
        L.task_id = T.id AND
        T.project_id = P.id AND
        P.owner_id = O.id");

$myResult = R::convertToBeans('myResult', $result); 

R::convertToBeans will convert the results into Bean which can easily be traversed.