This is an archived copy of the forum for reference purposes

Problem with buildFromSQL()

posted by aris 9 years ago

I'm trying to use buildFromSQL to get a paginated record set. The test code is as follows:

...
$sql = "SELECT * FROM groups g 
        JOIN user_groups u ON u.id_group = g.id
        WHERE u.id_user = $id
        ORDER BY $col $dir LIMIT " . PAGE_LINES . " OFFSET $offset";
$groups = fRecordSet::buildFromSQL('Group', $sql);
...

This code results in a SQL query such as:

SELECT * FROM groups g JOIN user_groups u ON u.id_group = g.id WHERE u.id_user = 1 ORDER BY groupname asc LIMIT 25 OFFSET 0;

The query runs fine on console. However, running the test script I get the following error:

Fatal error: Uncaught exception 'fProgrammerException' with message 'The column specified, id_user, does not exist in the table groups' in /usr/local/share/php/flourish/fSchema.php:2201
Stack trace:
#0 /usr/local/share/php/flourish/fORM.php(647): fSchema->getColumnInfo('groups', 'id_user', 'type')
#1 /usr/local/share/php/flourish/fActiveRecord.php(1942): fORM::objectify('Group', 'id_user', '1')
#2 /usr/local/share/php/flourish/fActiveRecord.php(1125): fActiveRecord->loadFromResult(Object(fResult)) 
#3 /usr/local/share/php/flourish/fRecordSet.php(492): fActiveRecord->__construct(Object(fResult))
#4 /usr/local/share/php/flourish/fRecordSet.php(349): fRecordSet->__construct('Group', Object(fResult), NULL)

I don't know why it's looking for 'id_user' column in the 'groups' table. Am I missing some specific Flourish syntax for SQL queries?

Thanks in advance, A.

Well, I keep trying to get the data of 'groups' and 'user_groups' joined tables.

I have no problem getting the groups of a user with a build such as:

$groups = fRecordSet::build(
    'Group',
    array (
        'user_groups.id_user=' => $id,
        'active='              => TRUE
    ),
    array(
        $col                   => $dir
    ),
    PAGE_LINES,
    $page_number
);

However, the resulting recordset lacks the group information related to user role (stored in user_groups table).

Using a double build such as:

$groups = $user->buildUserGroups()->buildGroups();

I get the same result, that is, the groups recordset related to the user_groups recordset, but without user_group data at all.

So, the SQL way looks as the route to follow. However, the fRecordSet::buildFromSQL() method seems to fail on getting the data of two tables, as well, as shown above. Actually, using a syntax such as:

SELECT g.*, u.* FROM groups g, user_groups u WHERE u.id_group = g.id AND u.id_user = 1

it fails with the message:

Fatal error: Uncaught exception 'fProgrammerException' with message 'The SQL statement specified, SELECT g.*, u.* FROM groups g, user_groups u WHERE u.id_group = g.id AND u.id_user = 1, does not appear to be in the form SELECT * FROM table'

This message seems to indicate that it is unable to recognize the reference to two tables in the query.

Then, what the best method would be to get a recordset with the result of the fields in both of the joining tables?

TIA,

posted by aris 9 years ago

Well, I've seen that the documentation on fRecordSet says "the SQL statement should select all columns from a single table", so this route seems closed. Finally, this is the way I have got to get the data from the two tables:

In the controller part:

$usergroups = fRecordSet::build(
    'UserGroup',
    array (
        'id_user=' => $id,
        'active='  => TRUE,
    ),
    array(),
    PAGE_LINES,
    $page_number
);
        
$usergroups->precreateGroups('id_group');

And in the template:

...
foreach ($this->usergroups as $ug) {
    // Get group data related to this usergroup
    $group = $ug->createGroup('id_group');

    ?>
    <tr class="<?php echo fCRUD::getRowClass($ug->getId, $affected) ?>">
        <td>
             <?php echo $group->prepareGroupid() ?>
        </td>
        <td>
            <?php echo $group->prepareGroupname() ?>
        </td>
        <td>
            <?php echo $group->prepareType() ?>
        </td>
        <td>
            <?php echo $ug->getRole() ?>
        </td>
    </tr>
}
...

The odd thing is that I get the most of the data from the $groups recordset. However, I can't do the other way around (build the groups recordset first and creating the usergroups recordset later) because groups -> usergroups is a one-to-many relation.

If there is a simpler way to get the same result, while keeping pagination and sorting, I'll be glad to hear from it.

posted by aris 9 years ago

An fActiveRecord object models a single row in a single table in the database. The database schema is introspected and the class is configured based on the columns in a table. This introspection means that if you need to pull in data from multiple tables that multiple objects will need to be created.

In your first post, you are selecting data from two tables, groups and user_groups. When the Group class is looping over the column to unescape them it runs into a column that doesn't exist in the groups table and appropriately throws an exception. As a side note, for security purposes you are going to want to use fDatabase placeholders for placing user input into your SQL strings since raw embedding opens you up to SQL injection.

In your third example, it appears to me that you've reached the correct algorithm to get the data you need. And yes, you can only create a record in a many-to-one or one-to-one relationship since there is a single record on the other end of the relationship. For many-to-many and one-to-many you need to perform a build since there can be multiple and you will get an fRecordSet back.

One other thing to note is that you don't need to pass id_group to precreateGroups or createGroup unless there are multiple associations between the groups and user_groups table.

I hope this helps some.

posted by wbond 9 years ago