Filtering a table based on another table

[rhino]

I have two tables - one that has a list of users, and one that has equipment checked out to them. I would like a list view that restricts the table to those users with something checked out to them and none of the others.
I would use this Select to see what I want, is there a way to do this in xataface? I am pretty new to the php programming... Thanks for the help!

SELECT PESTuser.* FROM PESTuser, PESTequip where PESTuser.PrimaryKey=PESTequip.Pestkey;

[shannah]

Hi,

I'm not sure I fully understand what you want to do yet, but I'll try to answer anyways. So it looks like you are wanting a list of equipment (not users), but you want a user to see only equipment that he has checked out. Is that correct.

The equipment table records the user id of the user that has checked the equipment out, so you probably want to set up a security filter on the equipment table so that users can only see the equipment that they have checked out.

You can do that by adding the following in the init() method of your equipment table delegate class.

Code: Select all

function init(&$table){
    $auth =& Dataface_AuthenticationTool::getInstance();
    $user =& $auth->getUser();
    if ( $user ){
        $table->setSecurityFilter(array('PestKey'=>$user->val('PrimaryKey')));
    }
}


Alternatively you could just do this with a relationship on the users table. E.g in the users table you would have a relationship called 'equipment' defined in your relationships.ini file as follows:

Code: Select all

[equipment]
    __sql__ = "select * from PESTequip where Pestkey='$PrimaryKey'"


[rhino]

Sorry that I was not clear...

I have 6000+ users, but only about 3000 pieces of equipment checked out... most users have nothing checked out and some have lots checked out... I would like one the views of the users table to only include users who have checked at least one piece of equipment out, so the view only will have ~1000 rows, and all of those rows will have some equipment checked out to the user. Using relationship.ini, I have the table mostly looking like the way I want, but I basically want to restrict the number of rows so that I dont have to dig through all of them to figure out which ones have equipment.

ex.
Table PESTuser
ID username phone location
1 frank 555-1234 place
2 bob 555-4321 place
3 tom 555-2345 place

Table PESTequip
ID PID Equip
1 2 Video Stuff
2 2 TV Stuff

the view of PESTuser I would like would only have 1 row returned: Bob, not all 3 users.

Thanks for your help!
Ryan

[shannah]

OK. Here's a sneaky way to do it. You can graft a field onto your users table called num_rentals that tracks the number of equipment rentals the user currently has as follows.

In the users fields.ini file:

Code: Select all

__sql__ = "select u.*, e.num_rentals from users u left join (select user_id, count(*) as num_rentals from equipment group by user_id) e on u.user_id=e.user_id"


Just swap your table and column names into this query (I can't remember what your exact column/table names were).

Then you can do a find on num_rentals just like you do on any other field.
You could search for users with 0 rentals, 1 rental, 2 rentals, greater than 1 rental, between 2 and 3 rentals, etc....

-Steve

[rhino]

Code: Select all

__sql__ = "select u.*, e.num_rentals from users u left join (select user_id, count(*) as num_rentals from equipment group by user_id) e on u.user_id=e.user_id where num_rentals > 0"

Does exactly what I want! Thanks for your help!

[shannah]

It isn't a good idea to put the "where num_rentals > 0" into the __sql__ clause. Rather indicate this in your queries through the Xataface UI. I.e. use the find form or include in the url:

e.g.
index.php?-table=users&num_rentals=>0

The __sql__ directive is meant to replace the standard SQL query to select rows of this table. It should result in a one-to-one and onto of the default query (i.e. have the same number of rows).

-Steve

[rhino]

thanks for the tip, I will do that. when I attempt to search for something using the find tab, I get this:

Code: Select all

Fatal error: Call to undefined method PEAR_Error::getField() in /home/merrillj/public_html/xataface/Dataface/Table.php on line 2480

but I can search using the seach box up in the right corner just fine, have any ideas?

[shannah]

what is the resulting url after the search that produces this error?

[rhino]

http://www.website.com/PESTome/index.php

[shannah]

So there is no query string in the URL? (i.e. this happens on your main page - not in the find form).

The error look like the error occurs when loading a field from a relationship. Can you post the relevant relationships.ini definitions for that table.

[rhino]

Thanks for pointing me in the right direction... according to you in another thread, my relationship names cannot have spaces in them, that seems to have fixed it.
thanks again
Ryan