Association query message table appendixId Multiple messages are associated with the uuid of a Chatappendixs table. Only the first one can find the Chatappendixs content, and the remaining few messages cannot be found

[xuyongweb]

Title

Association query message table appendixId Multiple messages are associated with the uuid of a Chatappendixs table. Only the first one can find the Chatappendixs content, and the remaining few messages cannot be found
// Add your title here

Description

Association query message table appendixId Multiple messages are associated with the uuid of a Chatappendixs table. Only the first one can find the Chatappendixs content, and the remaining few messages cannot be found
// Add your description here

Note: Description should contains the query, the current output & the expected output. You can use idbstudio to generate the example.
`getListWithMsg(data, limit, name) {

this.tableName = name ? name : this.tableName

let as = {}

Object.keys(MessageTable.columns).forEach(key => {

  if (Object.keys(ChatappendixTable.columns).includes(key)) {
    as[key] = ChatappendixTable.name + key
  }

})

let body = {

  from: this.tableName,
  where: data,
  order:{
    by: 'Messages.timestamp',
    type: 'desc' //supprted sort type is - asc,desc
  },
  join: {
    with: 'Chatappendixs',
    on: 'Messages.appendixId=Chatappendixs.uuid',
    type: 'left',
    as: as
  }
}

if(limit) {
  body.limit = limit
}

return connection.select(body);

}`

[ujjwalguptaofficial]

I don't understand the question - can you please give me what query you are runing and what you are expecting ?

[ujjwalguptaofficial]

install idbstudio and view your data - https://github.com/ujjwalguptaofficial/idbstudio

I feel somewhere your data is being updated to null

[xuyongweb]

I found the problem. The main and secondary tables were checked correctly on the reverse, but the data was checked on the opposite side of SQL file

[xuyongweb]

https://ujjwalguptaofficial.github.io/idbstudio/?db=Demo&query=select(%7B%0A%20%20%20%20from%3A%20%22Customers%22%2C%0A%20%20%20%20join%3A%20%7B%0A%20%20%20%20%20%20%20%20with%3A%20%22Orders%22%2C%0A%20%20%20%20%20%20%20%20type%3A%20%22inner%22%2C%0A%20%20%20%20%20%20%20%20on%3A%20%22Orders.customerId%3DCustomers.customerId%22%2C%0A%20%20%20%20%20%20%20%20as%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20customerId%3A%20%27cId%27%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%7D)

`select({

from: "Orders",

order: {
    by: 'Orders.customerId',

    type: 'asc' //supprted sort type is - asc,desc

},
join: {
    with: "Customers",

    type: "left",

    on: "Orders.customerId=Customers.customerId",

    as: {

        customerId: 'cId'
    }

}

})`

The main table is Orders, and the secondary table is the customerId of the Customers main table. The associated secondary table is customerId
It should be that one data in the main table corresponds to multiple data in the secondary table, but currently only the first data in the main table is associated with the secondary table, and the rest are null

[ujjwalguptaofficial]

this is because you are using left join which means you want all data from first table even if data does not match in second table. If you want all data matched then use inner join.

I hope this makes sense.

[xuyongweb]

Thinks，

this is because you are using left join which means you want all data from first table even if data does not match in second table. If you want all data matched then use inner join.

I hope this makes sense.
Thinks
But there is also a scenario where if the main table Orders.customerId is empty, using 'inner join' will not be able to find data with empty main table Orders.customerId, just like some chat data have attachments, some do not have attachments, and some chat data share the same attachment, but using 'inner join' can only find data with attachments, and data without attachments cannot be found

[xuyongweb]

select Messages.* , Chatappendixs.id as Chatappendixsid,Chatappendixs.uuid as Chatappendixsuuid,Chatappendixs.content as Chatappendixscontent from Messages left join Chatappendixs on Messages.appendixId=Chatappendixs.id where 1=1 and Messages.extra != 10070 and Messages.type='group' and Messages.unread=1 order by Messages.timestamp desc

The above is the syntax for 'sqlite'. The 'left join' of sqlite means that messages without attachments, messages with shared attachments, and messages with only one attachment can be found

[ujjwalguptaofficial]

I understood what you are trying to say. Let me spend some more time and came back but seems like its a bug.

[ujjwalguptaofficial]

Fixed in v - 4.5.5. please check now.

here is idbstudio link - https://ujjwalguptaofficial.github.io/idbstudio/?db=Demo&query=select(%7B%0A%20%20%20%20from%3A%20%22Orders%22%2C%0A%0A%20%20%20%20order%3A%20%7B%0A%20%20%20%20%20%20%20%20by%3A%20%27Orders.customerId%27%2C%0A%0A%20%20%20%20%20%20%20%20type%3A%20%27asc%27%20%2F%2Fsupprted%20sort%20type%20is%20-%20asc%2Cdesc%0A%0A%20%20%20%20%7D%2C%0A%20%20%20%20join%3A%20%7B%0A%20%20%20%20%20%20%20%20with%3A%20%22Customers%22%2C%0A%0A%20%20%20%20%20%20%20%20type%3A%20%22left%22%2C%0A%0A%20%20%20%20%20%20%20%20on%3A%20%22Orders.customerId%3DCustomers.customerId%22%2C%0A%0A%20%20%20%20%20%20%20%20as%3A%20%7B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20customerId%3A%20%27cId%27%0A%20%20%20%20%20%20%20%20%7D%0A%0A%20%20%20%20%7D%0A%7D)%20%20

[xuyongweb]

@ujjwalguptaofficial
Thank you very much! I'm sorry to reply to you so late for the Dragon Boat Festival holiday three days ago, but your problem solving efficiency is really gratifying. Thank you again for solving my problem perfectly!

[xuyongweb]

select * from table where ( a = 2 and b = 1 ) or ( a = 1 and b = 2 )

I also have a question about how to use jsstore to rewrite sqlite like this

The results of the jsstore query seem different from those of sqlite

[ujjwalguptaofficial]

it became all or in the or query, try with array -

select({
    from: 'Orders',
    where: [{
        customerId: 2,
        employeeId: 7
    }, {
        or: {
            customerId: 7,
            employeeId: 2
        }
    }]
})

https://ujjwalguptaofficial.github.io/idbstudio/?db=Demo&query=select(%7B%0A%20%20%20%20from%3A%20'Orders'%2C%0A%20%20%20%20where%3A%20%5B%7B%0A%20%20%20%20%20%20%20%20customerId%3A%202%2C%0A%20%20%20%20%20%20%20%20employeeId%3A%207%0A%20%20%20%20%7D%2C%20%7B%0A%20%20%20%20%20%20%20%20or%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20customerId%3A%207%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20employeeId%3A%202%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%5D%0A%7D)

[ujjwalguptaofficial]

Don't forget to stars the repo :)

[xuyongweb]

select({ from: 'Orders', where: [{ customerId: 2, employeeId: 7, type: 1, delete: false }, { or: { customerId: 7, employeeId: 2, type: 1, delete: false } }] })

If there are other conditional queries, should we write two copies in both the outer layer and the or, for example: delete: false, type: 1