php - 有人可以建议一种使用带有多个“where”子句的 Doctrine (QueryBuilder) 进行此查询的方法吗?
问题描述
我是 QueryBuilder 的新手,我正在尝试执行一个 POST 请求(使用 JSON)来检索我的数据库中的一些信息。
我使用数组是因为每个属性都可以有多个值。这是我当前发送的 JSON:
{
"name":["Martin"],
"state":["Ohio", "Texas"],
"job":["Photographer", "Reporter"]
}
这是我的数据库:
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
我想要的请求:
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%"
而我目前在 Symfony 中所做的事情并没有按照我想要的方式工作:
$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 . '%');
}
}
}
我的目标是得到这个结果:
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
我的代码运行不正常,它不会发送任何错误,而只会返回ID : 8
。
我正在寻找一种方法来执行仅返回ID : 1, 2, 8 and 12
.
非常感谢您未来的回答。
PS:我正在运行 Symfony 4.3.11。
解决方案
以下似乎是符合您要求的有效查询:
SELECT *
FROM application
WHERE SPEAK_FRENCH = 1
AND NAME = "Martin"
AND STATE = IN("Ohio","Texas")
AND (JOB LIKE "%Photographer%" OR JOB LIKE "%Reporter%");
另外,请参阅在数据库列中存储分隔列表真的那么糟糕吗?
推荐阅读
- node.js - 我有一个错误'TypeError:无法读取未定义的属性'leave''
- ansible - 如何在ansible中将字典初始化为某个值
- javascript - 使用 javascript 运行客户端 powershell 脚本
- python - 如何在 Python 中应用形态学运算来填充内部有孔的二元轮廓?
- python - Python asyncio:RuntimeError 在当前事件循环以外的事件循环上调用的非线程安全操作
- javascript - 如何使用具有两个值的 find()
- reverse-engineering - AARCH64/ARMv8 上没有堆栈溢出漏洞?
- c# - 使用 HttpClient POST 请求上传文件返回 200,但文件未上传
- google-bigquery - 通过 BigQuery 连接
- azure-devops - 来自单个 azure 管道 yaml 的多个 (github) PR 检查