DataMapper - how to perform basic query

DataMapper - how to perform basic query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: DataMapper - how to perform basic query (/showthread.php?tid=42682)

DataMapper - how to perform basic query - El Forum - 06-15-2011

[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.

DataMapper - how to perform basic query - El Forum - 06-15-2011

[eluser]WanWizard[/eluser]

Code:
$u = new User(1);

$u->setting->where_join_field($u, 'value', 'has_allowed')->get();

See the documentation.

DataMapper - how to perform basic query - El Forum - 06-15-2011

[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.

DataMapper - how to perform basic query - El Forum - 06-15-2011

[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).

DataMapper - how to perform basic query - El Forum - 06-15-2011

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

DataMapper - how to perform basic query - El Forum - 06-15-2011

[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