relatiosnhips or valuelists.ini

[pie]

hello

I'm messing around with relationships and needs some help.

I have 2 tables.

table 1 : user (user_id, name)
table 2: calender (calender_id, user_id, date)

now, i want to display the records of calender as follows

id - user name - date where user name is the name retrieved of table user, using the primary key user_id.

Now I wonder if I miust user relationships.ini of can I do this with fields and valuelist.ini?

[shannah]

They are used for different things. You could use both here.

e.g. you might have a relationship from users to calendars to represent the calendars that belong to a particular users. And you might use a valuelist for the user_id field of the calendars table to selecting the user when editing a calendar record directly.

[pie]

Ok, but I have no need to display a valuelist.
I just want to display the table 'calendar' where I replace the user_id by the name of the user, retrieved by the relation user_id.
It's a 1 to 1 relation and I don't want to display the 'relation' in the calender view.

I think this is easy, but I don't manage to find it

[pie]

OK, I solved it with a view

[FractalizeR]

Which view? Can you please elaborate?

[shannah]

So you just want user_id to show up as username in the calendar table? You can either use a valuelist on the user_id field for this, or you can use the __sql__ directive in the calendar fields.ini file to graft on a username field. Using a view is necessary in some cases, but it is certainly not necessary here.

[pie]

yes shannah, that's what I want to do.
a valuelist didn't work, because that gave me a user to choose, where username should be fix.
I also tried it with the __sql__in the fields , but did'nt found the right syntax.

so I created a view, something like this
select c.calendar_id, u.user_id, u.username from calendar c inner join user u on u.user_id = c.user_id

and then I defined my view in my conf file

[ADobkin]

pie, for your valuelist.ini, did you try something like this?

[Users]
__sql__ = "SELECT user_id, username FROM app_users"

That should dynamically reference the correct user name in your calendar table without requiring a view.

For more details, refer to Example 3 on this page:
http://www.xataface.com/documentation/t ... valuelists

This page may also be helpful:
http://xataface.com/wiki/valuelists.ini_file

[pie]

@ ADobkin: yes I used the valuelist as described, but then I receive a drop down list. Which is normal, because the valuelist gives the user an option to chose from. Where in my case, there is a 1 to 1 relation where one may not have the option to choose..

So that's why I used a view which conatins a union relation between the two tables.

[ADobkin]

There are a few other solutions as shannah mentioned, in case you want to do it within Xataface rather than using a view:

1) Use the valuelist.ini as indicated above, and then set the field permissions to be read-only. This should remove the drop-down selection list. This page explains how to set the field permissions:

http://xataface.com/wiki/How_to_granula ... each_field

2) Alternative to setting read-only permissions, you could use widget:type = static in your fields.ini file to make the field non-editable. See this page for more details on field directives:

http://xataface.com/wiki/fields.ini_file

3) Lastly, you could use a grafted field. The syntax is explained here:

http://xataface.com/wiki/Grafted_fields

Of course, you could just stick with your view also, but these are some of the other methods that can be used directly within Xataface without creating a view.

Alan

[pie]

@ ADobkin: Ok, I also made it working with the valuelist. I made a stupid mistake.. But I noticed one advantage when using a view.

Both the view and the valuelist gives me the same table view, but when I do an edit of one record, the view gives me a full username where the valuelist gives me the primary key corresponding to the full username. This is normal I think...

[shannah]

Careful... I'm not sure if the view will actually let you edit the value if it is from a join... Test out to make sure the edit works.

If you use a valuelist, you can use a select widget to make the editing a little more user friendly.