SUB QUERY or CTE query is not working

[parthshah2211]

Confirmation Checklist:

• [X] You have searched for an answer in the manual.
• [X] You have considered using the forum for general discussions, which can be more suitable for non-urgent or broad queries.
• [x] You are aware of our community support channels on Slack, Telegram EN, and Telegram RU, where you can interact with other users and our developers.
• [X] You know about Manticore Team's professional services. Engaging with our experts through a support subscription can significantly accelerate resolution times and provide tailored solutions to your specific needs.

Your question:

SELECT id,m_id,b_id,status,dp.price as dp_price,SUM(IF(SUBSTRING_INDEX(dp.price, '$', -1),SUBSTRING_INDEX(dp.price, '$', -1),0)) AS total_dp_price FROM lists WHERE  
            status IN ('NEEDS_REVIEW') AND 
          l_id = 692381349893783561 AND GROUP BY b_id
           ORDER BY updated_at DESC  FACET m_id,b_id,status,dp.price as dp_price ORDER BY COUNT(*) DESC ; SELECT COUNT(*) AS count FROM lists;

this is my working query but it affects the entire data due to GROUP BY I can’t get data for the same value of,m_id,b_id, and status. I want to apply a sub-query for the sum of total_dp_price

I can do this manner but getting an error SELECT l.id, l.marketplace_id, l.brand_id, l.status, l.data_points.price AS data_point_price, dp.total_data_point_price FROM listings l, JOIN ( SELECT brand_id, SUM( IF( SUBSTRING_INDEX(data_points.price, ‘$’, -1) REGEXP ‘^[0-9.]+$’, CAST(SUBSTRING_INDEX(data_points.price, ‘$’, -1) AS DECIMAL(10, 2)), 0 ) ) AS total_data_point_price FROM listings WHERE status = ‘NEEDS_REVIEW’ AND licensor_id = 692381349893783561 GROUP BY brand_id ) dp ON l.brand_id = dp.brand_id WHERE l.status = ‘NEEDS_REVIEW’ AND l.licensor_id = 692381349893783561 ORDER BY dp.total_data_point_price DESC, l.updated_at DESC LIMIT 10;

Config File: With indexing & other config

const indexConfigOptions: string[] = [
  "min_infix_len = '3'",
  "ignore_chars = 'U+AD,U+002D,U+00AD,U+002F,U+2044'",
  "charset_table = '0..9, A..Z->a..z, a..z'"
];

const config = {
  connectionLimit: 10,
  host: MANTICORE_HOST!,
  port: MANTICORE_PORT,
  multipleStatements: true,
  debug: (sql: string) => {
    console.log("------------------------- MANTICORE QUERY -------------------------");
    console.log(sql);
    console.log("------------------------- MANTICORE QUERY -------------------------");
  }
};

export const manticore = {
  Relevance: new RelevanceModel(config, "relevance", relevanceFields, indexConfigOptions, MANTICORE_MAX_MATCHES),
  Listing: new ListingModel(config, "listings", listingFields, indexConfigOptions, MANTICORE_MAX_MATCHES)
};

Listing Model:

export const listingFields: FieldType[] = [
  {name: "id", type: "bigint", fullText: false},
  {name: "licensor_id", type: "bigint", fullText: false},
  {name: "brand_id", type: "bigint", fullText: false},
  {name: "data_points", type: "json", fullText: false},
];

this kind of error ERROR 2013 (HY000): Lost connection to MySQL server during query ERROR 1064 (42000): P01: syntax error, unexpected identifier, expecting $end near 'l,

Any Help Would Be Appreciated:

Your assistance in resolving this issue or providing guidance on how to achieve sub-query with this query

[sanikolaev]

this is my working query

AND GROUP BY looks wrong. It should fail like this:

mysql> drop table if exists t; create table t(f text, a int); insert into t values(1, 'a', 1); select * from t where a = 1 and group by a;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table t(f text, a int)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t values(1, 'a', 1)
--------------

Query OK, 1 row affected (0.01 sec)

--------------
select * from t where a = 1 and group by a
--------------

ERROR 1064 (42000): P01: syntax error, unexpected BY near 'by a'

Can you please simplify your use case by removing from it everything which doesn't relate? Best if you can do it like the above example: create table first, then insert one or multiple docs, then run your query to demonstrate the issue.

[parthshah2211]

this is my working query

AND GROUP BY looks wrong. It should fail like this:

mysql> drop table if exists t; create table t(f text, a int); insert into t values(1, 'a', 1); select * from t where a = 1 and group by a;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table t(f text, a int)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t values(1, 'a', 1)
--------------

Query OK, 1 row affected (0.01 sec)

--------------
select * from t where a = 1 and group by a
--------------

ERROR 1064 (42000): P01: syntax error, unexpected BY near 'by a'

Can you please simplify your use case by removing from it everything which doesn't relate? Best if you can do it like the above example: create table first, then insert one or multiple docs, then run your query to demonstrate the issue. I did not understand what are you trying to say ?

[tomatolog]

I did not understand what are you trying to say ?

you need create minimal reproducible example with minimal documents count and query without unnecessary parts that could reproduce you case on the empty manticoresearch