DataMapper ORM v1.8.1

[eluser]Damir Sivic[/eluser]
thanks

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

[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

[eluser]WanWizard[/eluser]
Can you give me the exact query that is generated? Use $p->check_last_query() to dump it.

[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` = 1

Thanks

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

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

[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);
    }

to:

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);
    }

in order to also use ALL

Also add a where_all function.

[eluser]Spir[/eluser]
But where_all is not in Active Record Class.

[eluser]Spir[/eluser]
Try this:

Code:

foreach ($this->input->post('qualifications') as $val)
{
        $p->person->where_related('qualification/qualificationtype', 'id', $val);  
}
$p->person->get();