This is an archived copy of the forum for reference purposes

SQL query not working within fRecordSet::buildFromSQL()

posted by aris 9 years ago

Hello.

The following SQL query is running fine when executed on console to retrieve the names of those groups acting as parents of another group:

SELECT g.id, group_name, role, parent_id, 
(SELECT group_name FROM groups p WHERE g.parent = p.id) AS parent_group
FROM groups g 
JOIN user_groups ON user_groups.id_group = g.id 
JOIN roles ON user_groups.id_role = roles.id 
WHERE user_groups.id_user= 3 ;

And it results in a table such as the following one:

 id |   group_name   | role  | parent_id | parent_group  
----+----------------+-------+-----------+--------------
  8 | Test group     | Owner |           |
  9 | Another group  | Owner |           |
 12 | A subgroup     | Owner |         8 | Test group
(3 rows)

However, this same SQL query doesn't run within a fRecordSet::buildFromSQL() call, resulting in the error message 'The SQL statement specified ... does not appear to be in the form SELECT * FROM table'.

Changing the query to: "SELECT *, (SELECT group_name FROM groups p WHERE g.parent = p.id) AS ..." doesn't make any difference, so it looks as the second SELECT is causing the problem.

BTW, the table doesn't have any foreign key self-referencing itself (not all groups have a parent group), and any self-join constructed query returns only the groups having a parent.

There would be any other way to get this recordset built?

An fRecordSet object is a collection of fActiveRecord objects. fActiveRecord represents exactly one row from a table in the database. You have to select every column in a table for fActiveRecord to work properly. You can't select any extra, or exclude any.

In your situation you a either going to need to use fDatabase::translatedQuery() to get the data back, or use the fActiveRecord API to create the parent object and get the name. You can make getting the parent object more efficient by requesting them in batch:

// I don't know your DB schema, so the where conditions may need to be adjusted
$groups = fRecordSet::build('Group', array('users.id_user=' => 3));

// This tells the fRecordSet to create all parent objects in a single SQL call
$groups->precreateGroups();

foreach ($groups as $group) {
    echo 'Parent name: ' . $group->createGroup()->getName();
}

posted by wbond 9 years ago

Thanks for your response!

The problem is somewhat complex, because there are three tables around, 'user_groups' referencing 'users', 'groups' and 'roles', and 'groups' referencing itself:

groups
------
id
name
parent references groups(id)
...

user_groups
-----------
id_user references users(id)
id_group references groups(id)
id_role references roles(id)

roles
-----
id
role
...

The goal here is to get a recordset with the name of all the groups a given user (id_user) is member of, with his role in each of these and the name of the parent group, if it exists. I can't use the groups table as you said because of the error 'The table groups is not in a *-to-one relationship with the table roles'.

Alternatively, I'm using the user_groups table for the query because from there I have relationships with all the other tables. Let us say something as (a very simplistic coding):

$usergroups = fRecordSet::build('UserGroup', array('users.id_user=' => 3));
$usergroups->precreateGroups();
$usergroups->precreateRoles();
foreach ($usergroups as $ug) {
   $role  = $ug->createRole();
   $group = $ug->createGroup();
   $parent_group = $group->createGroup();
   echo $group->getName() . ' ' . $role->getRole() . ' ' . $parent_group->getGname();
};

This runs fine. You will note, however, that I'm precreating Groups and Roles only, but I'm not sure how could I do to precreate the second "Groups" (for parents), although that $parent_group = $group->createGroup(); within the loop is making the trick.

Another issue is related to sorting, as I'm in doubt about how to reference the parents column for fCRUD::getColumnClass and fCRUD::printSortableColumn, given that groups.name is already referencing the group names column, of course.

posted by aris 9 years ago

Since the parent group is two associations away from the user_groups tables, you won't be able to precreate it. Flourish does use an identity map though, which means a record will only be loaded out of the database once per request, except when the record is loaded via fRecordSet (since all records are loaded in a single request). If you can't get the appropriate performance out of the ORM, it might be necessary to ditch it for plain SQL queries for this one situation.

In terms of the ordering, you can order via tables up to two associations away using the once-removed syntax (related_table=>once_removed_table.column) that is shown on the fRecordSet API page. Here is an example:

// This should order by the parent group name
array('groups=>groups.name' => 'asc')

In terms of referencing it with fCRUD, I would use something like parent_group_name and translate that to groups=>groups.name in your database model(s).

posted by wbond 9 years ago

In terms of referencing it with fCRUD, I would use something like parent_group_name and translate that to groups=>groups.name in your database model(s).

Great! I assume that translation should be done in the configuration method of the model. However, I have not found what precise class/method to use to do that. fORMColumn seems to be restricted to a few configureXxx methods and I don't see any examples in neither fActiveRecord or fORM for that matter.

posted by aris 9 years ago

Well, in this case you are using a full call to fRecordSet::build(), so it accepts the $order_bys array so there isn't a configuration directive to override it. If you we using the built in fActiveRecord->build{RelatedRecords}() method, then you can set the order by with fORMRelated::setOrderBys().

In my fActiveRecord classes, I add static methods to handle building related records that require special conditions and/or order bys.

class User extends fActiveRecord
{
    static public function buildActive($order_by, $direction)
    {
        if ($order_by == 'parent_group') {
            $order_by = 'groups=>groups.name';
        }
        $order_bys = array($order_by => $direction);
        return fRecordSet::build(__CLASS__, array('status=' => 'Active'), $order_bys);
    }
}

posted by wbond 9 years ago