CODEWITHSUNDEEP
javaandroidperformancesqlite
Victor Apoyan
Victor Apoyan

Reputation: 65

Optimizing SQLite query to use one query

Problem Description

I'm writing application which is working with database, it select some data from the database and shows it to the user in the list view. There are some criterias which I must keep while selecting items from the database.

  1. In the database I keep some location (latitude and longitude), when selecting items I must get my current position and order items from the closer to me to the farther.
  2. In the database I have TOP companies which must be always first in the list independent on their location (latitude and longitude)

Question

So I need some query which will select all this information in the right ordering at once, because currently I am using 3 queries to do that.

Current solution

Here are the queries which I use to select all information which I need, currently I select all items add them to array and only after that I sort them in the array according to the distance.

  1. SELECT TOP COMPANIES

     select CompanyInfo._id, CompanyInfo.name_en, ContactInfo.telephones, ContactInfo.location, CompanyInfo.websites, ContactInfo.address_en, ContactInfo.location, ContactInfo.position, TopFirmsByCode.Level, ContactInfo.name_en from CompanyInfo, ContactInfo, TopFirmsByCode where CompanyInfo._id=TopFirmsByCode.RegNo AND CompanyInfo._id=ContactInfo._id AND TopFirmsByCode.Code = 452 order by TopFirmsByCode.Level ASC, CompanyInfo.rating_ordering_en ASC`

  2. SELECT THE REST OF COMPANIES ORDERED BY DISTANCE

     select CompanyInfo._id, CompanyInfo.name_en, ContactInfo.telephones,ContactInfo.location,CompanyInfo.websites,ContactInfo.address_en,ContactInfo.location, ContactInfo.position, ContactInfo.name_en from CompanyInfo, ContactInfo where CompanyInfo._id=ContactInfo._id AND CompanyInfo.category_codes LIKE %547% order by ContactInfo.location ASC

Upvotes: 1

Views: 51

Answers (1)

Karakuri
Karakuri

Reputation: 38585

Give this a try:

select CompanyInfo._id, CompanyInfo.name_en, ContactInfo.telephones, 
    ContactInfo.location, CompanyInfo.websites, ContactInfo.address_en,
    ContactInfo.position, ContactInfo.name_en, TopFirmsByCode.Level,
    (case when TopFirmsByCode.Code = 452 then 1 else 0 end) as isTopFirm
from CompanyInfo
join ContactInfo on (CompanyInfo._id=ContactInfo._id)
join TopFirmsByCode on (CompanyInfo._id=TopFirmsByCode.RegNo)  
where (TopFirmsByCode.Code=452 OR CompanyInfo.category_codes LIKE %547%)
order by isTopFirm DESC, TopFirmsByCode.Level ASC, CompanyInfo.rating_ordering_en ASC

Upvotes: 1

Related Questions