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

Select and then insert with query builder

Hello how can i create and Select and Insert into with query builder.

I have a query builder with an searchCriteria which works. In this query a user can filter other users by country and region -> so get user with ID 100, 200, 300 for example.

users = $this->modelsManager->createBuilder()
    ->from(['u' => 'Vokuro\Models\Users'])
    ->join('Vokuro\Models\SearchCriteria'  , "s.country = u.country AND u.region  LIKE CONCAT(SUBSTRING(s.zipcode, 1, 1),'%')",'s')
    ->where('s.usersId = :id: AND u.id != :id:',array('id' => $id))
    ->orderBy('u.id')
    ->getQuery()
    ->execute();

Now i want to extract the users Id's from this object and insert the ids into another table for example INFO. Is it possible to extend this query? And how?

Rgds



32.2k

dirty way? 

$ids = array_column($users->toArray(false), 'id');
$placeholders = array_fill(0, count($ids), '?');
$di->getShared('db')->execute('insert into info (id) values ('.implode(',', $placeholcers).')', $ids);

Zero elegance, 100% functional ¯\(ツ)/¯ Good luck



59.9k

Elegance doesn't matters :-)

Thx for your help, but i get an error:

 SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1

In $ids i have 4 ID's now and in $placeholders 4 x ? ... In my table i have 4 columns id = autoincrement usersId clickedId infotext

I need to fill the 4 given ID's now in usersId, clickedId is the loggedUser and infotext is 0

Thx again :-)



59.9k
Accepted
answer

@emiliodeg,

maybe i found a way, i will check this:

I changed my Info table with composite key, so the pair of usersId and clickedId are unique

CREATE TABLE info(
   id int NOT NULL  AUTO_INCREMENT, 
   usersId int NOT NULL,
   clickedId int NOT NULL,
   date int NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (usersId, clickedId)
);

And now i check if data exists:

INSERT INTO info
            (usersId,
             clickedId,
             date) 
SELECT bl.usersId, bl.clickedId, bl.date FROM user AS bl
ON DUPLICATE KEY UPDATE
    usersId = VALUES (usersId);

If data don't exists -> INSERT If data exists -> UPDATE

In my mysql console it works perfectly, but i have to translate it into "phalcon" right now :-)

What do you think about that? Could this be a way?

Rgds



32.2k

it's the best solution, even you don't need to check duplicate data with that query



59.9k

It is working and if it is working it is good and good is very good :-))))

Thx again!