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

Merge two tables

I dont know if it's me, but I don't seem to be fetching the tables, the way I want to.

I have to tables SuccessLogins and FailedLogins

I need to print every SuccessLogins and every FailedLogins, that have the same userid

But when I use leftJoin, it just merge to table with the same result?

Let's say that I have 2 successLogins and 2 FailedLogins, then it only prints out 2 results, when I need 4?



43.9k

Hi,

please show us your code.



145.0k

It looks like you need to do union select. Phalcon don't support it, you just need to use raw sql.



2.8k

The ideal is to have a Logins parent table, and 2 complementary tables for success cases and failed

logins = {id, date}, successlogins = {loginsId, ...}, failedlodings = {loginsId, message,...}

You'd use the find method in logins and get an array that may contain object of different classes (successlogins|failedlodings|logins)

But this is impossible in the phalcon ORM, you have to use raw sql for everything that is not basic CRUD for the moment. Other PHP ORMs can map better the relations to objects. But in general, ORMs are leaky abstractions, you'll have to use sql anyway at some point.

If both successLogins, FailedLogins, youll need to add some identifier like SELECT 1 AS category, successLogins.* FROM successLogins UNION ALL SELECT 2, FailedLogins.* FROM FailedLogins, otherwise, you'll not be able to recognize their types in PHP.



43.9k