I have been unable to query objects based on a property in an array of objects.
I am trying to query all orders that have the event with id 7:
const orders = await this.orderRepository.find({where: {events: {elemMatch: {'id': event.id}}}});
The above gives me the following error:
ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''{\"id\":\"7\"}
If i try the following filter, I always get an empty array back:
{where: {events: {like: '%id%'}}}
What is the correct approach for Loopback 4?
UPDATE:
I am using MySQL 8.0.
This is the definition of events in my order model:
@property({
type: 'array',
itemType: 'object',
required: false,
})
events: CartItem[] | null;
Since you are using the MySQL
loopback connector to connect to your MySQL
database, currently this connector treats both String/JSON
as VARCHAR
. As such, you could try the following modification to like
{where: {events: {like: '%id:'+7+'%'}}}
or
const orders = await this.orderRepository.find({
where: {
events: {
like: '%id:'+event.id+'%'
}
}
});
or using regular expressions
const orders = await this.orderRepository.find({
where: {
events: {
regexp: '.*id:'+event.id+'.*'
}
}
});
const orders = await this.orderRepository.find({
where: {
events: {
regexp: new RegExp(".*id:"+event.id+".*")
}
}
});
in an attempt to match the json
pattern {
id:7,name:'Event 7'}
where in this case the value inside id
could be 7
.
Based on your question and the mysql error shown, the following assumptions were made:
Schema (MySQL v5.7)
create table samples(id int primary key auto_increment, events varchar(400));
insert into samples(events) values
('[{id:3,name:\"Boscobel\"},{id:4,name:\"Rays\"}]'),
('[{id:7,name:\"Boscobel 7\"},{id:8,name:\"Rays 8\"}]');
Query #1
select * from samples where events like '%id\:7%';
| id | events |
| --- | ----------------------------------------------- |
| 2 | [{id:7,name:"Boscobel 7"},{id:8,name:"Rays 8"}] |
Query #2
select * from samples where events like '%id:7%';
| id | events |
| --- | ----------------------------------------------- |
| 2 | [{id:7,name:"Boscobel 7"},{id:8,name:"Rays 8"}] |
Query #3
select * from samples where events like '%id\:70%';
There are no results to be displayed.
Query #4
select * from samples where events like '%id:200%';
There are no results to be displayed.
View on DB Fiddle
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With