DataMapper - how to perform basic query

[eluser]Genki1[/eluser]
In DataMapper, how do I program the following simple query?

My data:
I have a table "USERS" with a many-to-many relationship to table "SETTINGS".
In the join table, there is an additional field "VALUE".

My query:
For user id "1", where the setting name is "has_allowed", show the "value"

Database structure:
table: USERS
fields: id, etc.

table: SETTINGS
fields: id, name

join table: SETTINGS_USERS
fields: id, setting_id, user_id, value

What I've tried:

I have tried this, but it returns all records related to user id "1", not just the single "has_allowed" record that I want:

Code:

$u = new User();
$u->where('id', 1)->get();  // filter condition

$u->include_related('setting', array('name'), FALSE, TRUE); // include "name" column in result set
$u->where_related_setting('name', 'has_allowed'); // filter condition
$u->setting->include_join_fields();  // include "value" column in result set

$u->setting->get();

Result: returns MULTIPLE records and WITHOUT the join field. I want a single record WITH the join field.

[eluser]WanWizard[/eluser]

Code:

$u = new User(1);
$u->setting->where_join_field($u, 'value', 'has_allowed')->get();

See the documentation.

[eluser]Genki1[/eluser]
Hi,

Thanks for the reply but that does not solve the query. The proposed solution:

1. searches for "VALUE = 'has_allowed'" but I want to search for "NAME = 'has_allowed'" and then display the VALUE field.

2. the column "VALUE" is not shown in the result set.

I have been reading and re-reading the documentation before posting here and I've spent hours trying to get this to work.

[eluser]WanWizard[/eluser]
Ok, so I misunderstood your question. Try

Code:

$u = new User(1);
$u->setting->where('name', 'has_allowed')->include_join_fields()->get();

Note that the field will be called 'join_value' (to avoid collisions).

[eluser]Genki1[/eluser]
Perfect. DataMapper + WanWizard = Beautiful solution!

[eluser]Genki1[/eluser]
And, to assist others, the solution provided above is the shorthand for this:

Code:

$u->setting->where('name', 'has_allowed');  // filter the related table "settings"
$u->setting->include_join_fields();  // include columns from the join table in the result set
$u->setting->get();                  // perform the query
echo $u->setting->join_value;        // display the field "value" from the join table