Cannot convert value of type string to integer when joining multiple tables

Need Help
#1

Hello everybody,

please could someone have a look on my probabely simple problem? I have three tables: People, Rulings and Direps_Rulings, where direp is and alias for a person from People table.

join
join744×405 22.2 KB

I created a testing debug function in my SubjectsController.php which should list all people, who are related to subject through rulings for a given subject id:

querry_code
querry_code950×663 29.8 KB

When I run function, I get correct SQL query:

querry_code
querry_code950×663 29.8 KB

When I want to call the query and obtain all results(I just add ->all() method), I get error:

querry_code
querry_code950×663 29.8 KB

querry_all
querry_all1487×786 55.9 KB

My question is? What’s wrong with my code?

When I run the SQL query in HeidiSQL, I am getting the correct results:

heidi
heidi1269×749 90.3 KB

Please can anybody help me? Thank you in advance.

#2

Seems Cake thinks that something is supposed to be an integer field, but it’s a string. Any chance you recently changed an integer field in the database to a string, but didn’t clear the ORM cache after doing so?

#3

Thank you @Zuluru for your help. Finally, the error was in 'conditions' =>['People.id' =>'dirrul.direp_id']; It should be: 'conditions' =>'People.id =dirrul.direp_id'; Here is the solution:

DEBUG_WORKING
DEBUG_WORKING933×761 29.8 KB

#4

As you can see in the docs an associative array is only used if you want to use literal values (like true, a specific number, datetime or whatever) in your join condition.
https://book.cakephp.org/3/en/orm/query-builder.html#adding-joins

But if you want to reference other tables columns you need to put it all in one string.

#5

Thank you :+1: I can’t believe that it took me several days to make one stupid join. But thanks to all of you and this forum I did it!

#6

You might feel even worse when you realize that once you’ve configured your associations properly, all you need is:

$query = $this->People
    ->find()
    ->distinct()
    ->innerJoinWith('Rulings')
    ->where(['Rulings.subject_id' => $id]);
#7

And using ‘bake’ 5 minutes?

#8

Using the Bake CLI requires you to have a table structure named according to the CakePHP Naming Conventions.
https://book.cakephp.org/4/en/intro/conventions.html#cakephp-conventions

If you do that then all your associations etc. are auto-detected and added automatically.

#9

In this case, just (?):

  • tablename ‘people’ to ‘peoples’
  • fieldname ‘direp_id’ to people_id’

So, I guess since user_looser is building the database, it’s not a problem to do the renaming?

As I replied in the other post, my advice is to focus on the database and do multiple runs of the bake-command until you’re stuck on functionality (Think you still have to get the subjects into the play?).

There won’t be bugs, code is on the place where it has to be (e.g. joins in models), you can show your boss a first take of the app.

At that point there will be the need for more functionality (e.g. authentication etc.), at that point you can start work on that.

Just pointing to this because I hope you will keep liking CakePHP. Working several days on a join wouldn’t make me happy.

#10

Thank you for your advices. drtrppr why do you propose to use “peoples”? Is not People plural form of Person?

#11

Wonderful, thank you ndm. You must always have a idol and your code is idol code for me now. I try to make it your way.

#12

oops!
person will be fine.