[SOLVED] List Prefilter with userid saved into another table + SOLUTION

[marcq]

Hi,

I need to create a prefilter with another table.

I have a booking table : "fab_booking" (among other the "id" field which is the booking id or number which I need to "link" to the "parent_id" field of the "fab_booking_repeat_book_crewchief" table)

and a table that contains the list of all skippers that has enrolled to for a specific booking (sailing tour booking) : "fab_booking_repeat_book_crewchief" (we can have several skippers for one booking). This table is feed with a databasejoin element.

I would like to retrieve all bookings from the "fab_booking table" where ('{$my->id}') of the logged in user exists in the "fab_booking_repeat_book_crewchief" table.

"id" of the fab_booking should be "linked" to the "parent_id" field of the "fab_booking_repeat_book_crewchief" table in order to get the appropriate bookings.

"id" of the logged in user is saved in the "book_crewchief" field of the "fab_booking_repeat_book_crewchief" table.

Would appreciate your help.

Cheers, marc

 

[cheesegrits]

What is the name of the table the book_crewchief join element links to?

-- hugh

 

[marcq]

What is the name of the table the book_crewchief join element links to?

-- hugh

Hi Hugh, thank you for you reply.
The table is fab_crew_chief
Cheers, marc

 

[cheesegrits]

So "book_crewchief" is a field on the fab_crew_chief table, not on the fab_booking_repeat_book_crewchief table?

-- hugh

 

[marcq]

So "book_crewchief" is a field on the fab_crew_chief table, not on the fab_booking_repeat_book_crewchief table?

-- hugh

Hi Hugh,

Thank you for your reply.

booking table : "fab_booking" (contains among other the "id" field which is the booking id that the "fab_booking_repeat_book_crewchief" table "parent_id" field inherit)

Database join element : "fab_crew_chief" table, value "userid" ("userid" value is the joomla user id that I'm using with the juser plugin to synchronize my application users with the joomla user table)

Enrolled skippers userids are saved into the : "fab_booking_repeat_book_crewchief" table.

Fields of "fab_booking_repeat_book_crewchief"

id
parent_id (this field is populated with the id inherited from the "id" field of the booking table "fab_booking" (booking id))
book_crewchief (this field is populated with the value of a database element ("fab_crew_chief" table, value "userid" (which is the joomla user id))

I would like to retrieve all bookings from the "fab_booking" table where the logged in user exists into the "fab_booking_repeat_book_crewchief" table, "book_crewchief" field.

Important : I'm not saving the skippers userids into the booking table.

It was working when I was using a picklist element since the skippers userids where saved into the "fab_booking" booking table, "book_crewchief" field (for example : [114,115]). But since I'm using a database join element (rendering checkboxes) which is more appropriate to my needs, userids are now saved into the "fab_booking_repeat_book_crewchief" table, "book_crewchief" field.

I tried several prefilter variants, but I guess it is because I need to have the userids saved into the main booking table "fab_booking" right ?

So could I also insert | update the skippers userids into the "fab_booking" table "book_crewchief", field using the PHP plugin in order to being able to prefilter my records according their {$my->id} userids ? This field stays empty since the values are now saved into the the "fab_booking_repeat_book_crewchief" table, "book_crewchief" field.

If yes, how can I save | update those userids into this "fab_booking" table, "book_crewchief" field since this field as the type "INT(11) "? I would have to change the type in order to save the userids, but I don't know if the appropriate way to do it.

Or do I need to create a new field element where I can also insert or update these userids ? if yes, what would be the appropriate way to do it ?

I hope I was clear enough since my issue is complex to me.

Thank you in advance for you support.

Cheers, marc

 

[marcq]

UPDATE (forget the previous post that was just no necessary since I thin that it should be possible to solve this issue with a query) :

I try to add a prefilter :

AND
id (which is the booking number)
IN (or EQUALS)
Value :

$db =& JFactory::getDBO();
$user = JFactory::getUser();
   $query = "SELECT * FROM fab_booking_repeat_book_crewchief WHERE book_crewchief = " . $user->id;
   $db->setQuery( $query );
   $bookings = $db->loadAssoc();

   return $bookings['parent_id'];

Type: Eval

Returned booking in fullcalendar belongs now to the logged in user.

The problem is that only one booking is returned instead of 3.

Any clue where the problem could be ?

Thanks in advance for your support.

Cheers,

marc

 

[marcq]

I could solve this issue, solution is :

Join : AND

Field : id (which is the booking number)
Condition : EQUALS
Value :

$db =& JFactory::getDBO();
$user = JFactory::getUser();

$query = "SELECT parent_id FROM fab_booking_repeat_book_crewchief WHERE book_crewchief = " . $user->id;
$db->setQuery( $query );
$bookings = $db->loadColumn();
// var_dump($bookings);exit;
return $bookings;

Type : Eval

 

[cheesegrits]

Sorry about not getting back to you, my weekend got bumped forward a day, so I was off Friday, and working today (Sunday).

-- hugh

 

[marcq]

Sorry about not getting back to you, my weekend got bumped forward a day, so I was off Friday, and working today (Sunday).

-- hugh

No problem Hugh