0

I'm new to the QueryBuilder and I'm trying to do a POST request (with a JSON) to retrieve some informations in my database.

I'm using array because each property can have several values. Here's the JSON I'm currently sending :

{
    "name":["Martin"],
    "state":["Ohio", "Texas"],
    "job":["Photographer", "Reporter"]
}

Here's my database :

ID  |   NAME        |   FIRST_NAME  |   STATE       |   JOB             |   SPEAK_FRENCH
1   |   Martin      |   John        |   Ohio        |   Photographer    |   1
2   |   Martin      |   Max         |   Ohio        |   Reporter        |   1
3   |   Martin      |   Sophie      |   Texas       |   Model           |   1
4   |   Alexander   |   David       |   Kansas      |   Author          |   0
5   |   Archie      |   Kira        |   Maine       |   Photographer    |   1
6   |   Lushen      |   Albert      |   Nevada      |   Pilot, Model    |   1
7   |   Wilkins     |   Minnie      |   Utah        |   Tailor          |   0
8   |   Martin      |   Thomas      |   Texas       |   Reporter        |   1
9   |   Patino      |   Stephen     |   Virginia    |   Pilot, Reporter |   1
10  |   Ting        |   Maria       |   Nevada      |   Dentist         |   0
11  |   Brown       |   Barbara     |   Virginia    |   Reporter        |   1
12  |   Martin      |   William     |   Texas       |   Photographer    |   1
13  |   Zachary     |   Thomas      |   Virginia    |   Telephonist     |   1

The request I would like to have :

SELECT * FROM 'application'
WHERE SPEAK_FRENCH = 1
AND NAME = "Martin"
AND STATE = "Ohio"
AND JOB LIKE "%Photographer%"
OR SPEAK_FRENCH = 1
AND NAME = "Martin"
AND STATE = "Ohio"
AND JOB LIKE "%Reporter%"
OR SPEAK_FRENCH = 1
AND NAME = "Martin"
AND STATE = "Texas"
AND JOB LIKE "%Photographer%"
OR SPEAK_FRENCH = 1
AND NAME = "Martin"
AND STATE = "Texas"
AND JOB LIKE "%Reporter%"

And what I have currently done in Symfony, doesn't work the way I want it to :

$repository = $this->getDoctrine()->getRepository(Application::class);
$query = $repository->createQueryBuilder('request');
$temp_name = 0;
$temp_state = 0;
$temp_job = 0;
foreach ($app->getName() as $name) {
    $temp_name = $temp_name + 1;

    $query = $query->orWhere('request.speakFrench = 1')
        ->andWhere('request.name LIKE :JSONname' . strval($temp_name))
        ->setParameter('JSONname' . strval($temp_name), $name);

    foreach ($app->getState() as $state) {
        $temp_state = $temp_state + 1;

        $query = $query->andWhere('request.state LIKE :JSONstate' . strval($temp_state))
            ->setParameter('JSONstate' . strval($temp_state), $state);

        foreach ($app->getJob() as $job) {
        $temp_job = $temp_job + 1;

        $query = $query->andWhere('request.job LIKE :JSONjob' . strval($temp_job))
            ->setParameter('JSONjob' . strval($temp_job), '%' . $job . '%');
        }
    }
}

My goal is to get this result :

ID  |   NAME        |   FIRST_NAME  |   STATE       |   JOB             |   SPEAK_FRENCH
1   |   Martin      |   John        |   Ohio        |   Photographer    |   1
2   |   Martin      |   Max         |   Ohio        |   Reporter        |   1
8   |   Martin      |   Thomas      |   Texas       |   Reporter        |   1
12  |   Martin      |   William     |   Texas       |   Photographer    |   1

My code isn't working well, it doesn't send any error but only return ID : 8.

I'm looking for a way to do the query that returns only the ID : 1, 2, 8 and 12.

Thanks a lot for your future answers.

P.S : I'm running Symfony 4.3.11.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Errias10
  • 23
  • 5
  • @Strawberry I would like to have a fully functionnal query that really work the way I want (return id 1, 2, 8 and 12). The code I posted in PHP return only the "last" query, like I have sent this json : `{ "name":["Martin"], "state":["Texas"], "job":["Reporter"] }` – Errias10 Aug 21 '20 at 14:02
  • If speed is what you need, building a raw sql string from your json fields would be the answer. If simplicity is what you want I sometimes do something like `->where('request.name LIKE "%theName%"')->andWhere('request.job = "job1" OR request.job = "job2'')` and so one. It would be different in your case but maybe I gave you some ideas. – Martin M. Aug 21 '20 at 14:06
  • I may be missing some subtle point here but I don't understand why the IN operator would not work for you: WHERE name IN (:names) AND state IN (:states) etc. DQL is nice in that it can handle array parameters for IN operators. – Cerad Aug 21 '20 at 14:15
  • @MartinM. I think I'll certainly use something like that yeah, thank you for your help. – Errias10 Aug 21 '20 at 14:17
  • Yes, I rarely need to use `IN` so I forgot but Cerad is correct. – Martin M. Aug 21 '20 at 14:31

2 Answers2

0

The following would appear to be a valid query corresponding to your requirements:

SELECT * 
  FROM application
 WHERE SPEAK_FRENCH = 1
    AND NAME = "Martin"
    AND STATE = IN("Ohio","Texas")
    AND (JOB LIKE "%Photographer%" OR JOB LIKE "%Reporter%");

Also, see Is storing a delimited list in a database column really that bad?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • It helps me, thanks. Does this works the same way if I have multiple names ? Do you have any ideas on how to do it using Doctrine QueryBuilder ? – Errias10 Aug 21 '20 at 14:15
  • Normalise your schema. Then you can use IN() (and indexes) for all the conditions. – Strawberry Aug 21 '20 at 14:18
0

With Doctrine you can do for exemple:

    $queryBuilder = $this->createQueryBuilder('request');
    $queryBuilder
        ->andWhere($queryBuilder->expr()->andX(
            $queryBuilder->expr()->eq('user.speakFrench', ':speakFrench'),
            $queryBuilder->expr()->like('request.name', ':name'),
            $queryBuilder->expr()->in('request.state', ':states'),
            $queryBuilder->expr()->in('request.job', ':jobs'),
        ))
        ->setParameter('speakFrench', true)
        ->setParameter('name', 'Martin')
        ->setParameter('states', ['Ohio', 'Texas'])
        ->setParameter('jobs', ['Photographer', 'Reporter'])
        ->getQuery()
        ->getResult();

That should be a good start.

To continue:

SwissLoop
  • 466
  • 2
  • 11