We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Trying to do a little special finding...

My User model is conected to Group model, which is connected to Grade model. I'm trying to find a condition (the WHERE clause) or criteria (https://docs.phalcon.io/es/latest/api/Phalcon_Mvc_Model_Criteria.html ) to get a string that is like Grade.name Group.name - User.fullName, and the search starts from the User model.

The problem is that, with the condition, I cannot do anything, 'cause I can't find if it is possible to do something like a JOIN there (I don't think so); and with the criteria, I don't find how to set the query.

Can anybody illuminate me?



2.5k

How do you have your models written up? You can also do the query using PHQL as well.

$query = new Phalcon\Mvc\Model\Query("SELECT * FROM User
INNER JOIN Group on User.GroupId = Group.Id
INNER JOIN Grade on User.Id = Grade.UserId
WHERE CONCAT(Grade.name, ' ', Group.name, ' - ', User.fullName) =  :name:", $this->getDI());

$value = $query->execute(array(
'name' => 'A Group - John Smith'
));


33.8k
Accepted
answer

The problem isn't how they are written; the problem is that I need to do a WHERE that is that CONCAT of the three tables.

BTW thanks, but I already did it with Criteria (I successed using the join(), didn't realize that the table name has to be the same that the model file name xD). Also your code fails 'cause of the second param of $query and something more I don't remember right now.



2.5k

Can you post your model definitions, I'd like to play around with them locally.



33.8k

Student.php

<?php

use Phalcon\Mvc\Model,
    Phalcon\Db\Column;

class Student extends Model
{
    public function initialize()
    {
        $this->belongsTo("group_id", "Group", "id");
    }
    public static function searchStudents($search = "")
    {
        $where = "CONCAT(Grade.name, " ", Group.name, " - ", Student.lastname1, " ", Student.lastName2, ", ", Student.name) LIKE CONCAT("%", :chain:,"%")";
        $params = array(
            "chain" => $search
            );
        $types = array(
            Column::BIND_PARAM_STR
            );
        $students = Student::query()
            ->join("Group", "Student.group_id = Group.id")
            ->join("Grade", "Group.grade_id = Grade.id")
            ->where($where)
            ->bind($params)
            ->bindTypes($types)
            ->execute();
        $results = array();

        foreach ($students as $student)
        {
            $results[] = array(
                "id" => $student->id,
                "chain" => ($student->group->grade->name . " " .
                    $student->group->descripcion . " - " .
                    $student->lastName1 . " " .
                    $alumno->lastName2 . ", " .
                    $student->name)
                );
        }

        return $results;
    }
}

Group.php

<?php

use Phalcon\Mvc\Model;

class Group extends Model
{
    public function initialize()
    {
        $this->belongsTo('grade_id', 'Grade', 'id');
        $this->hasMany('id', 'Student', 'group_id');
    }
}

Grade.php

<?php

use Phalcon\Mvc\Model;

class Grade extends Model
{
    public function initialize()
    {
        $this->hasMany('id', 'Group', 'grade_id');
    }
}


2.5k

Have you tried changing your model name of Group to StudentGroup, I was able to get the models to work like that.



33.8k

If you can post the code here, I will see it later, now I have to go (BTW, renaming? I don't have a table for that model)



2.5k

Here is the updated models:

<?php

use Phalcon\Mvc\Model,
    Phalcon\Db\Column;

class Student extends Model
{
    public function initialize()
    {
        $this->belongsTo("group_id", "StudentGroup", "id");
    }
    public static function searchStudents($search = "")
    {
        $where = "CONCAT(Grade.name, ' ', StudentGroup.name, ' - ', Student.lastname1, ' ', Student.lastName2, ', ', Student.name) LIKE CONCAT('%', :chain:,'%')";
        $params = array(
            "chain" => $search
            );
        $types = array(
            Column::BIND_PARAM_STR
            );
        $students = Student::query()
            ->join("StudentGroup", "Student.group_id = StudentGroup.id")
            ->join("Grade", "StudentGroup.grade_id = Grade.id")
            ->where($where)
            ->bind($params)
            ->bindTypes($types)
            ->execute();
        $results = array();

        foreach ($students as $student)
        {
            $results[] = array(
                "id" => $student->id,
                "chain" => ($student->group->grade->name . " " .
                    $student->group->description . " - " .
                    $student->lastName1 . " " .
                    $alumno->lastName2 . ", " .
                    $student->name)
                );
        }

        return $results;
    }

}
<?php

use Phalcon\Mvc\Model;

class StudentGroup extends Model
{
    public function initialize()
    {
        $this->belongsTo('grade_id', 'Grade', 'id');
        $this->hasMany('id', 'Student', 'group_id');
    }
}
<?php

use Phalcon\Mvc\Model;

class Grade extends Model
{
    public function initialize()
    {
        $this->hasMany('id', 'StudentGroup', 'grade_id');
    }
}

Also be careful when any of those columns are null a CONCAT willl return null.



33.8k

Updated? We did the same, only that my Group model isn't renamed xDD

I thinked you find a way to do the find() for this case, that's why I asked you to post your code hehe. Thanks anyway.