+- 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 ORM v1.8.1 (/showthread.php?tid=42440)
DataMapper ORM v1.8.1 - El Forum - 06-13-2011
[eluser]Damir Sivic[/eluser]
thanks
DataMapper ORM v1.8.1 - El Forum - 06-13-2011
[eluser]theprodigy[/eluser]
I believe I have the optgroups working. Just need a little more testing, and then I will update my page (the one Basketcasesoftware linked you to) with the new file and updated directions for use.
DataMapper ORM v1.8.1 - El Forum - 06-14-2011
[eluser]matyhaty[/eluser]
Where-Related issues:
Im creating a person search, which will search all persons in the system, for one or multiple qualifications that they have.
The relationship of the qualifications is:
Person relates to Qualifications
Qualifications related to Qualification Types
Thus, using a multiselect I process the selected qualification types required:
Code:
$selected = array();
foreach ($this->input->post('qualifications') as $val)
{
$selected[] = $val;
}I then query all persons (which is $p->persons) using where_related - to only get persons who have ALL of the requested qualification types.
Code:
$p->person->where_related('qualification/qualificationtype', 'id', $selected);This works fine, but gives me a OR operator (e.g. select * from blah blah where related_id = 1 || related_id = 2 || related_id = 3....) for all the qualification type IDs. How can this be done for AND?
Many Thanks
DataMapper ORM v1.8.1 - El Forum - 06-14-2011
[eluser]WanWizard[/eluser]
Can you give me the exact query that is generated? Use $p->check_last_query() to dump it.
DataMapper ORM v1.8.1 - El Forum - 06-14-2011
[eluser]matyhaty[/eluser]
Code:
SELECT DISTINCT `persons`.*
FROM (`persons`)
LEFT OUTER JOIN `persons_qualifications`
persons_qualifications ON `persons`.`id` = `persons_qualifications`.`person_id`
LEFT OUTER JOIN
`qualifications` qualifications ON `qualifications`.`id` =
`persons_qualifications`.`qualification_id`
LEFT OUTER JOIN `qualifications_qualificationtypes`
qualification_qualifications_qualificationtypes ON `qualifications`.`id` =
`qualification_qualifications_qualificationtypes`.`qualification_id`
LEFT OUTER JOIN
`persons_teamleafclients` persons_teamleafclients ON `persons`.`id` =
`persons_teamleafclients`.`person_id`
WHERE (
`qualification_qualifications_qualificationtypes`.`qualificationtype_id` IN ('3', '4')
)
AND
`persons_teamleafclients`.`teamleafclient_id` = 1Thanks
DataMapper ORM v1.8.1 - El Forum - 06-14-2011
[eluser]Spir[/eluser]
What about :
Code:
$selected = array();
foreach ($this->input->post('qualifications') as $val)
{
$selected[] = $val;
}
$sql = "SELECT DISTINCT `persons`.*
FROM (`persons`)
LEFT OUTER JOIN `persons_qualifications`
persons_qualifications ON `persons`.`id` = `persons_qualifications`.`person_id`
LEFT OUTER JOIN
`qualifications` qualifications ON `qualifications`.`id` =
`persons_qualifications`.`qualification_id`
LEFT OUTER JOIN `qualifications_qualificationtypes`
qualification_qualifications_qualificationtypes ON `qualifications`.`id` =
`qualification_qualifications_qualificationtypes`.`qualification_id`
LEFT OUTER JOIN
`persons_teamleafclients` persons_teamleafclients ON `persons`.`id` =
`persons_teamleafclients`.`person_id`
WHERE (
`qualification_qualifications_qualificationtypes`.`qualificationtype_id` ALL ('".implode("','", $selected)."')
)
AND
`persons_teamleafclients`.`teamleafclient_id` = ".teamleafclient->id;
$p = new Person();
$p->query($sql);DataMapper ORM v1.8.1 - El Forum - 06-15-2011
[eluser]Spir[/eluser]
So all you need is to use MySQL "ALL" instead of the Datamapper generated "IN". I don't think DM has the ability to generate "ALL" queries so you have to build SQL query yourself.
Am I write?
DataMapper ORM v1.8.1 - El Forum - 06-15-2011
[eluser]Spir[/eluser]
WanWizard maybe we could upgrade "_process_special_query_clause"
from:
Code:
/**
* Handles specialized where clauses, like subqueries and functions
*
* @ignore
* @param string $query Query function
* @param string $field Field for Query function
* @param mixed $value Value for Query function
* @param mixed $extra If included, overrides the default assumption of FALSE for the third parameter to $query
* @return DataMapper Returns self for method chaining.
*/
private function _process_special_query_clause($query, $field, $value, $extra = NULL) {
if(strpos($query, 'where_in') !== FALSE) {
$query = str_replace('_in', '', $query);
$field .= ' IN ';
} else if(strpos($query, 'where_not_in') !== FALSE) {
$query = str_replace('_not_in', '', $query);
$field .= ' NOT IN ';
}
if(is_null($extra)) {
$extra = FALSE;
}
return $this->{$query}($field, $value, $extra);
}Code:
/**
* Handles specialized where clauses, like subqueries and functions
*
* @ignore
* @param string $query Query function
* @param string $field Field for Query function
* @param mixed $value Value for Query function
* @param mixed $extra If included, overrides the default assumption of FALSE for the third parameter to $query
* @return DataMapper Returns self for method chaining.
*/
private function _process_special_query_clause($query, $field, $value, $extra = NULL) {
if(strpos($query, 'where_in') !== FALSE) {
$query = str_replace('_in', '', $query);
$field .= ' IN ';
} else if(strpos($query, 'where_not_in') !== FALSE) {
$query = str_replace('_not_in', '', $query);
$field .= ' NOT IN ';
} else if(strpos($query, 'where_all') !== FALSE) {
$query = str_replace('_all', '', $query);
$field .= ' ALL ';
}
if(is_null($extra)) {
$extra = FALSE;
}
return $this->{$query}($field, $value, $extra);
}Also add a where_all function.
DataMapper ORM v1.8.1 - El Forum - 06-15-2011
[eluser]Spir[/eluser]
But where_all is not in Active Record Class.
DataMapper ORM v1.8.1 - El Forum - 06-15-2011
[eluser]Spir[/eluser]
Try this:
Code:
foreach ($this->input->post('qualifications') as $val)
{
$p->person->where_related('qualification/qualificationtype', 'id', $val);
}
$p->person->get();