CODEWITHSUNDEEP
mysqlsqlfilter
rf jm
rf jm

Reputation: 33

Sort by character length but don't want filter

Wants to sort reviews with comment length <= 250 and >= 50

All short or long reviews should be at the end..currently i have to filter them... but this is not what I wants to have

current query

select
c.id,
c.name,
DATE_FORMAT(c.created,'%d %b %Y') as date_new,
r.ratings,
c.comments,
ROUND((r.ratings_sum / r.ratings_qty),1) as total_rating
from
commentsAS c , rating AS r , id_script i
where
c.pid = i.sub_cat_id
AND i.cat_id = 118
AND r.reviewid = c.id
AND c.published = '1'
AND LENGTH(c.comments) <= 250
AND LENGTH(c.comments) >= 50
ORDER BY c.created DESC

I don't want filter them with below

AND LENGTH(c.comments) <= 250
AND LENGTH(c.comments) >= 50

Upvotes: 0

Views: 29

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

If you use a new Version of MariaDB you can use a virtual persitent column. If you insert or change date they count direct new feld. You can also use a index or composite index on it.

Alter the table

ALTER TABLE comments
ADD COLUMN l INT AS (LENGTH(`comments`)) PERSISTENT,
ADD KEY  (`comments`);

Query

SELECT
  c.id, c.name,
  DATE_FORMAT(c.created,'%d %b %Y') as date_new,
  r.ratings, c.comments,
  ROUND((r.ratings_sum / r.ratings_qty),1) as total_rating
  FROM commentsAS c , rating AS r , id_script i
where
  c.pid = i.sub_cat_id
  AND i.cat_id = 118
  AND r.reviewid = c.id
  AND c.published = '1'
  AND c.l between 50 AND 250
  ORDER BY c.created DESC;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

In MySQL, you can do this simply using a boolean expression:

order by ( length(c.comments) < 50 or length(c.comments) > 250) desc

MySQL treats booleans in a numeric context as integers, with true as 1.

An alternative formulation is even shorter:

order by (length(c.comments) between 50 and 249)

Upvotes: 2

Tom H
Tom H

Reputation: 47464

ORDER BY
    CASE
        WHEN LENGTH(C.comments) > 250 OR LENGTH(C.comments) < 50 THEN 1
        ELSE 0
    END

Upvotes: 3

Related Questions