databasejoin & prefilter with {$my->id}

[jsg2510]

I have a simple user profile username, password and email

I have a table with details of books - I have used an element "entered by" of type user to successfully prefilter on enteredby(raw) to just view books that a specific user entered in to the database using {$my->id}

I have another element "owner" where I have done a databasejoin using "value = id(recommended)" from the profiles table. This gives allows the book owner to be a member of the system. I can see valid users under owner column (& see their id when I set label=id)

I want to create a view to show only books that the user owns. So I tried owner(raw) equals {$my->id} text registered users BUT I get an empty list. If I change the restriction to be all users then I get a populated list but it shows all books

I am a complete newbie to Fabrik - all help appreciated.

Thanks

 

[chozma]

Hi,

I think I had this problem too when I started out.

When you try to view the results what type of user are you, a registered user or a special (eg admin) user? Do you get the result you expect if you log in and view it as a registered user?

Could you post a screenshot of how you have got the settings for this?

I think you might need another rule for your prefilter to set it so that a special users see everything. There was a forum post on this a little while ago that gave the exact way of doing this so it might be worth having a look around the forums for that

Kind regards,
Hannah

 

[jsg2510]

Hi Hannah

Thanks for your help.

No I am logged in as a regular registered user - where I signed up at the Profiles public signup page - that works OK because I can filter correctly if the element is a native to my list - it fails because of the join.

A couple of screenshots showing the basics -

 

[chozma]

Are you sure you even need the database join? I don't understand how using that would allow your members to become assigned to the system. Could you give a bit more info? Perhaps give a list of the tables and the elements as well, it might help me understand your database design a bit more too.

In my system I have my table of users (the joomla table) and then it is enough that for each item the user creates their user id is stored using a user element. So in your case you have your table of books and users. Then each time your user creates a new book their user id number is stored in that table using a user element. The pre-filter checks their id against that column. No database join needed.

 

[jsg2510]

Like!! Thanks Hannah - that solves the simple problem - where I need 1 user attached to a record (example current owner of the book) - but I can't see how to make it work if there are several users associated (example waiting list) - in this case the user element seems to be restricted to a single entry when I need multiple people (does this mean that I need a dbjoin or is there another way?)

Firstly I have a Profiles with JUser - name/password/email

I want several sorts of user allocations on my book -

1. Owner - the user who has the book at the moment - & I do this in the way that you describe (thanks!). This works well using the prefilter but is a single user not multiple
2.Waiting List (could be several users owners) - this varies dynamically (so I don't want to use discrete user elements) - the valid users want to run a view "show books on my waiting list" People on waiting list must be a user with a Profile (in future we may change this to a series of guests)

I think that to do the waiting list that I need a databasejoin/select with $my> id (which I can't get to work yet) - but if you can see another way would be delighted - thank you!

 

[chozma]

I suggest that for your waiting list you will need a new table. This table will keep details about the users who are on the waiting list, this should be separate to the table about the books. It would probably contain elements that store the books' primary key (so the id) and the user's id. You need a separate row for each user and book.

You would use a database join element for these two fields (book id and userid) so that on your forms you get dropdowns that allow you to select the users or books. You should set the value to be the id (your primary key) and the label to be whatever else you want. For example with the books I would expect you would set the label to be the book title.

Strictly speaking I would also suggest that you consider not storing which user currently has the book in the same table as your book details. A better database design would be one table for the details about the book title, author, IBSN number etc), and then another table for people who have or want the books. For the second table you might end up with quite a few columns such as date book taken out, date returned, do they currently have it etc. That way you will also get a history of who had what books and when. I'm not sure you'd be able to get that history if you store who currently has the book in the book table.

Hope that makes sense and is helpful, let me know how you get on,

Hannah

 

[jsg2510]

Again - many thanks- I appreciate your time

So this was a good idea which I tried

& I also used the feature (which is a neat way of getting all book details and adding new elements without corrupting teh book data structure) where you can have a new list (say waitinglist) looking at the same table (book) with new elements (users from a databasejoin on the Profiles) - in both cases the databasejoin seems to stop the {$my->id} finding the correct id - the filter returns a null (when looking as logged on user with books against their name)

One half solution (as per your earlier post) is to add a user element to the waitinglist - it works well to see books where I am waiting but again this is a single entry field limiting the wait list to 1 person per book (unless I manually add x more elements)

So I have 2 possible solutions

Either
1. Understand how to do a dbjoin & configure so that the {$my->id} can see the correct userid (from JUser Profile table) and set up to allow multiples

Or

2. Understand how to have multiple user elements so that the waiting list can be unlimited length (not defined manually by me at the design) and that users can see books where they are on waitlist and other users who are also on list (& the order)

Any more thoughts? Cheers Hannah!

 

[chozma]

Well I really think you need to get the pre-filter sorted so you can go with option 1. Option 2 is a hack and is not a good design in my opinion. I just think it will just be a pain at every step for you.

Ok, well let's see what we can do to get the prefilter on your userid thingy sorted. Its so useful and you will be hamstringed again and again if you don't get it sussed. It took me ages to do as well (steep learning curve with Fabrik but worth it) but now its working its so worth it.

First things first, can you check in the database to see what exactly is being stored in your owners element eg the element than the {$my->id} is being checked against. It should be numbers.

The way you have set up the prefilter looks right to me from the screenshot, at least it has the same set-up as mine which works so it might be something to do with the values in the db??

Another thing to do is just keep everything really simple (if you haven't already). So turn off any other filters, anything else your in tables/elements are doing that is special. Something that caught me out at the beginning is that I had prefilters set on my list and then I also had a list as a menu item with other prefilters. The prefilters on the menu item cancelled out the prefilters on my list and it took me ages to work it out. So as I say, unpublish as much as you can to check its not anything else having a random impact you might not expect. Oh yes and make sure you are logging in as a registered user not an admin/super user/special user.

Any joy?

 

[jsg2510]

I think that all other filters are off.

The issue seems to be that the dbjoin does not preserve the id but gives it a local id. So I have a user test1 with id=472. If I access the users using user element then I can see id=472 which explains why in that case the filter works. But If I look at the id after dbjoin then test1 is allocated id=1 - so no surprise that the filter doesnt work.

Not at all clear to me what I can do on the dbjoin to get the system level id (I am using id = recommended key on import)

thanks for your help

 

[chozma]

Can I see a screenshot of what you have set the value and label to be in your dbjoin please?

 

[jsg2510]

sure

 

[chozma]

Ok, I think its because you are setting the label to be the id. What other options do you get in the label drop down menu? Maybe another screenshot so I can see too? You're looking for an option that looks like the user's id, not the id field of the row.

 

[jsg2510]

Not that - Label jsut changes what you see in the form - I have tried all the options - I just set it here to id so that I can identify that the id here and the global jsuer id are different

thanks

 

[chozma]

Ok have you tried changing the value to the user_id

or

changing the label to the user_id AND changing the pre-filter so it is looking up owner (without the raw bit on the end)

Either any good?

 

[jsg2510]

very sad

 

[chozma]

The only other thing I can suggest is that we talk tomorrow and I take a look at the database with you and we'll see if we can nail it? Do you by any chance have a webex account? Its going to be something really simple and you'll kick yourself when you work out what it is!

 

[cheesegrits]

One thing I noticed is that in an earlier post there is a screenshot of your juser plugin setting, and you don't have anything selected for "User ID Field", which means Fabrik won't be saving the newly created user ID in your "profile" table.

I'm moving this thread to Standard support, so it gets some attention from Fabrik staff.

-- hugh

 

[troester]

Several remarks:
you table ( _profiles) and element names ( _owner) are looking very strange, they should start with a character.

Linking to users:
Why do you link to your profile table and not directly to Joomla's user table?
A dbjoin to the profile table , value= id, label = whatever will store the profile record id, which is surely not the Joomla user id.
You can set the label to the element storing the Joomla user id, do you have such an element (see Hugh's comment)?
Then you could prefilter Owners = {$my->id} (not Owners(raw))
But such a label is not very informative, so better use a dbjoin element going directly to #_users (value=id - which is the Joomla userid in this case, label=name or username)

As chozma says: for your waiting list you'll need an extra table (m:n) linking book ids and waiting users.
You can create a new Fabrik list with e.g elements id, bookid, waiting-user-id
Or you can create a new group in you book form, set it to repeatable, move the waiting-user element into this group (then Fabrik will create such a table automatically).

 

[jsg2510]

Superb

I was only using the Profiles as I am newbie and assumed that this was the intention - you learn through your mistakes

dbjoining owner to cnr_owners table and then using Owners(raw) = {$my->id} solved the problem

Thanks!