logo
down
shadow

Order result following the order of an array of words


Order result following the order of an array of words

By : Kizito Ignitious Mug
Date : November 17 2020, 11:52 AM
should help you out Include the CASE in your select thus naming the column and then use it in your order clause, like so:
code :
case_str = "CASE WHEN users.flag = 'z_word' THEN 0"
case_str += "    WHEN users.flag = 'b_word' THEN 1"
case_str += "    WHEN users.flag = 'a_word' THEN 2"
case_str += "    WHEN users.flag = 'c_word' THEN 3 ELSE 4 END AS sorty_flag"

User.select("*, ?", case_str).order("sorty_flag").to_a


Share : facebook icon twitter icon
How to order query result according to the order of array elements?

How to order query result according to the order of array elements?


By : ktheo
Date : March 29 2020, 07:55 AM
around this issue Basics:
PostgreSQL unnest() with element number
code :
WITH t AS (
   SELECT *
   FROM   unnest('{0,579489,579482,579453,561983,561990,562083}'::int[])
                  WITH ORDINALITY AS t(id, rn)
   )
(
SELECT id
FROM   question
JOIN   t USING (id)
ORDER  BY t.rn
)
UNION ALL
(
SELECT id
FROM   question
LEFT   JOIN t USING (id)
WHERE  t.id IS NULL
AND    status IN (1, -1) 
AND    created_at > 1426131436
ORDER  BY id DESC
LIMIT  10
);
MySQL: how to order a result if certain records contain specific words

MySQL: how to order a result if certain records contain specific words


By : Sam
Date : March 29 2020, 07:55 AM
it fixes the issue I would like to show a query result displaying titles which contain Summer festival and Summer festivals ordered first in the list of records. , Try conditional order by
code :
SELECT title 
FROM items
ORDER BY CASE WHEN title LIKE '%Summer festival%' THEN 1 ELSE 2 END, title
Different search words order should have same result in Symfony query

Different search words order should have same result in Symfony query


By : Javier García
Date : March 29 2020, 07:55 AM
wish of those help Currently, my search query search only exact match and correct order. But I need that order of words is not important. , This is what is the content of your $searchTermPartial
code :
//%PSG%%Neymar%
CREATE TABLE public.rol
(
  id integer NOT NULL,
  role character varying(255) NOT NULL,
  CONSTRAINT rol_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.rol
  OWNER TO postgres;
    foreach ($searchTerms as $key => $term) {
//.....
                $qb->andWhere($qb->expr()->like('r.role', ':term' . $key))
                    //.....
                    ->setParameter('term' . $key, '%' . $term . '%');
//.....
            }
select * from rol where role like '%PSG%' and role like '%Neymar%'
How to sort result set in order of matching words

How to sort result set in order of matching words


By : user3180614
Date : March 29 2020, 07:55 AM
This might help you You can implement the match query using combination of Span First, Span Term and Span Near Query
For the sake of simplicity, I've created a sample index with only one field labeled name of type text along with the below documents.
code :
POST sortindex/_doc/1
{
  "name": "Heinz A. Meyer"
}

POST sortindex/_doc/2
{
  "name": "Heinz Meyer GmbH Heizung-Sanitär"
}

POST sortindex/_doc/3
{
  "name": "Heinz Meyer"
}

POST sortindex/_doc/4
{
  "name": "Karl-Heinz Meyer GmbH"
}
POST sortindex/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "span_near": {               <---- Span Near Query
            "clauses": [
              {
                "span_term": {         <---- Span Term Query
                  "name": {
                    "value": "heinz"
                  }
                }
              },
              {
                "span_term": {
                  "name": {
                    "value": "meyer"
                  }
                }
              }
            ],
            "slop": 4,                 <---- Retrieve all docs having both heinz and meyer with distance of <= 4 words
            "in_order": true           <---- Heinz must always come before Meyer 
          }     
        }
      ],
      "should": [
        {
          "span_first": {              <---- Span First Query
            "match": {
              "span_term": {           <---- Span Term Query
                "name": "heinz"
              }
            },
            "end": 1                   <----  Retrieve docs having heinz's postition <= 1 and > 0 i.e. the first word
          }
        }
      ]
    }
  }
}
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4,
      "relation" : "eq"
    },
    "max_score" : 0.38327998,
    "hits" : [
      {
        "_index" : "sortindex",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 0.38327998,
        "_source" : {
          "name" : "Heinz Meyer"
        }
      },
      {
        "_index" : "sortindex",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 0.26893127,
        "_source" : {
          "name" : "Heinz Meyer GmbH Heizung-Sanitär"
        }
      },
      {
        "_index" : "sortindex",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 0.25940484,
        "_source" : {
          "name" : "Heinz A. Meyer"
        }
      },
      {
        "_index" : "sortindex",
        "_type" : "_doc",
        "_id" : "4",
        "_score" : 0.19908611,
        "_source" : {
          "name" : "Karl-Heinz Meyer GmbH"
        }
      }
    ]
  }
}
When querying using OPENJSON, will the result set mimic the order of the JSON array or will the order be random?

When querying using OPENJSON, will the result set mimic the order of the JSON array or will the order be random?


By : user3729143
Date : March 29 2020, 07:55 AM
it should still fix some issue In case of JSON array and OPENJSON() call with default schema, based on documentation, the result is a table with columns key, value and type, and the key column holds the index of the element in the specified array. If you want to get an ordered set, you should use a statement like the following:
code :
DECLARE @json nvarchar(max) = N'[{ "Field": "1" }, { "Field": "2" }, { "Field": "3" }]'

SELECT [value]
FROM OPENJSON(@json)
ORDER BY CONVERT(int, [key])

-- or

SELECT c.Field
FROM OPENJSON(@json) j
CROSS APPLY OPENJSON(j.[value]) WITH (Field int '$.Field') c
ORDER BY CONVERT(int, j.[key])
Related Posts Related Posts :
  • Why can't I have ruby code and comments in Embedded Ruby?
  • Rails: How can I make an object available in all views?
  • Cannot enter simply form information into SQLite DB (Rails)
  • Rails - Retain form values on failed submission. Getting "First argument in form cannot contain nil or be empty&quo
  • How to handle STI and build related models automatically?
  • Suppress an error when saving a record in Rails
  • Rails AJAX reload the page. Why?
  • How to pass user attributes to action mailer
  • Custom Validator to Prevent an appointment from being scheduled too early in day?
  • Postgres Enable TCP/IP Connection
  • How do I avoid the circular argument reference warning in activesupport
  • Unable to push to heroku master because of rails/spring version error
  • deep nested routes in rails
  • Best approach to Users Profile Images in Rails App
  • RailsInstaller: Can I maintain the gem fold when re-installing a new version?
  • couldn't find file 'jquery-ui' i get this error even after having this file in my application.js
  • Ruby regex to split user and date text input
  • Tool like BetterErrors for JRuby?
  • redirect_to in action destroy ruby on rails
  • Where are the I18n.t link and reference paths?
  • Sending emails to multiple recipients in rails app
  • Rails, Devise, Sign up = write name in other Database
  • pass custom attribute on rspec factory_girl controller test
  • rails generate uses wrong version of Ruby
  • Duplicate Requests from a Browser
  • One action in Rails controller sends no data back
  • Local data (JSON) in Middleman inside a different path than /data
  • How to pass numbers in an array into a SQL statement
  • ActiveAdmin - Allow non-logged-in users to view but not edit resources
  • How to refactor this Model logic in Rails
  • :readonly key of belongs_to method doesn't work
  • Create a new view in directory
  • nested resources in devise
  • Yaml value with a leading `!`
  • Rails admin show username instead of User #5
  • Can't start WEBrick with Byebug on
  • Failing Rspec test of a FactoryGirl object
  • engine yard No application found matching remotes:
  • Rails 4 sessions cookie is not set
  • How do I flush a class method in Rails 4 using memcached?
  • AJAX form submitting in HTML rails 4
  • Using greater than or less than in a Rails Active Record query
  • Searching Post by Origin & Destination locations using Geokit-Rails
  • Deploying application to two different servers, with different production.rb contents
  • Spree with deface - Use deface to replace contents of block
  • I need to change date part of DateTime variable
  • Ruby on Rails 4 - Using Rake on Heroku
  • Ruby symbol setup
  • rescue inside an each_line code block
  • RoR/Squeel - How do I use Squeel::Nodes::Join/Predicates?
  • Using a different key name for an association attribute in rails api active model serializer
  • Running callbacks in rspec
  • Ruby on rails: wrong number of arguments (Devise and parsley-rails gems)
  • Query with Date.today returns two days
  • select all the values of the records belonging to a specific field of the database and save them in a array Active Recor
  • RSPEC test index action with before_action filter
  • Rails model columns and performance
  • keys and values from an ajax POST are all escaped
  • What is the formula for setting WEB_CONCURRENCY on heroku?
  • How to select objects based on their enum value and then sum another field belonging to the object collection in Rails
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org