React Native SQLite not returning query with LIKE ? and query a column based on array of text?

Question

Doing this query:

select * from menuitems

return new Promise((resolve, reject) => {
 db.transaction((tx) => {
  tx.executeSql(
    "select * from menuitems",
    [`%${query}$%`],
    (_, { rows }) => {
      useDevLogData(rows._array, "filterByQueryAndCategories rows._array");
    }
  );
}, reject);
});

I have this set of result:

[
  {
    "id": 1,
    "uuid": "1.0",
    "title": "Spinach Artichoke Dip",
    "price": "10",
    "category": "Appetizers"
  },
  {
    "id": 2,
    "uuid": "2.0",
    "title": "Hummus",
    "price": "10",
    "category": "Appetizers"
  },
  ...
]

But when i do it like this i have no result: returning empty []

 return new Promise((resolve, reject) => {
    db.transaction((tx) => {
      tx.executeSql(
        "select * from menuitems WHERE title LIKE ?",
        [`%${query}$%`],
        (_, { rows }) => {
          useDevLogData(rows._array, "filterByQueryAndCategories rows._array");
        }
      );
    }, reject);
  });

Also I would need to combine this search text, e.g. %Spinach% query to filter category on an array of categories, e.g. ["Appetizers", "Salads"], How do I query to filter the category column based on array of string? and also based on that, I need to query the field title? I need to do something like:

"select * from menuitems where title LIKE %Spinach% AND category LIKE ["Appetizers", "Salads"]"

GowthamGnanajothi · Accepted Answer · 2023-12-05 15:12:13Z

1

@gpbaculio You have to add parenthesis surrounding column in your answer to handle search to be effective on one or more category column.

tx.executeSql("select * from menuitems where title like ? AND (category = ? OR category = ? OR category = ?)",
    [`%${query}%`, ...activeCategories], (_, { rows }) => {
      resolve(rows._array);
    });

To dynamically handle category you can use map like below

tx.executeSql(
      `select * from menuitems where title LIKE '%${query}%' AND (${activeCategories.map((cat) => 
        `category='${cat}'`).join(' OR ')})`,[], (_, { rows }) => {
          resolve(rows._array);
        });

Hope this helps!

answered Dec 5, 2023 at 15:12

GowthamGnanajothi

893 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gpbaculio · Accepted Answer · 2023-03-15 22:53:19Z

0

Sorry have typo, resolved it like this, I have 3 MAX category:

activeCategories = [
  "Appetizers"
]

 return new Promise((resolve, reject) => {
    db.transaction((tx) => {
      tx.executeSql(
        "select * from menuitems WHERE title LIKE ? AND category = ? OR category = ? OR category = ?",
        [`%${query}%`, ...activeCategories],
        (_, { rows }) => {
          useDevLogData(rows._array, "filterByQueryAndCategories rows._array");
        }
      );
    }, reject);
  });

Might help someone like me new to SQL, this seem works for me now

answered Mar 15, 2023 at 22:53

gpbaculio

5,97814 gold badges70 silver badges115 bronze badges

Collectives™ on Stack Overflow

React Native SQLite not returning query with LIKE ? and query a column based on array of text?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related