Need Help about SQLite3UIQuery

coblongpamor · 2012-05-03 15:43:52

I think, SQLite3UIQuery not handle sftid type of field with a sub select query.

ADDRESS_SELECT = 'StreetLine1,StreetLine2,StreetLine3,RTRW, '+
    '(SELECT Name FROM Country WHERE ID=Country) AS Country, '+
    '(SELECT Name FROM Province WHERE ID=Province) AS Province, '+
    '(SELECT Name FROM Regency WHERE ID=Regency) AS Regency, '+
    '(SELECT Name FROM SubDistrict WHERE ID=SubDistrict) AS SubDistrict, '+
    '(SELECT Name FROM Village WHERE ID=Village) AS Village ';

I also found that qoContains less accurate.
Could you check it?

many thanks.

ab · 2012-05-03 15:51:21

I do not have code to reproduce this problem easily.

Perhaps could you post something (or email me), or be more specific about the possible fix to be implemented?
Is it because e.g. the "Country" column does not have an easy way to filter the entry?

coblongpamor · 2012-05-03 16:24:47

I did not do anything on SQLite3UIQuery, just create an instance on the new form.

TSQLBase = class(TSQLRecord)
  private
    fCode: RawUTF8;
    fName: RawUTF8;
    fIsInactive: Boolean;
    fTimeLog: TTimeLog;
    fCreatedTime: TCreateTime;
    fModifiedTime: TModTime;
  protected
  public
    property Code: RawUTF8 read fCode write fCode;
    property Name: RawUTF8 read fName write fName;
    property IsInactive: Boolean read fIsInactive write fIsInactive;
    property TimeLog: TTimeLog read fTimeLog write fTimeLog;
    property CreatedTime: TCreateTime read fCreatedTime write fCreatedTime;
    property ModifiedTime: TModTime read fModifiedTime write fModifiedTime;
  end;

TSQLBaseRegion = class(TSQLBase)
  private
    fLatitude: Double;
    fLongitude: Double;
  protected
  public
  published
    property Code stored False;
    property Name;
    property Latitude: Double read fLatitude write fLatitude;
    property Longitude: Double read fLongitude write fLongitude;
  end;

  TSQLBaseRegionClass = class of TSQLBaseRegion;

  TSQLCity = class(TSQLBaseRegion)
  private
    fCityClass: TSQLBaseRegionClass;
  protected
  public
  published
    property CityClass: TSQLBaseRegionClass read fCityClass write fCityClass;
  end;

  TSQLCountry = class(TSQLBaseRegion)
  private
    fCity: TSQLCity;
    fPrimaryLanguage: TSQLLanguage;
  protected
  public
  published
    property City: TSQLCity read fCity write fCity;
    property PrimaryLanguage: TSQLLanguage read fPrimaryLanguage write fPrimaryLanguage;
  end;

  TSQLProvince = class(TSQLBaseRegion)
  private
    fCountry: TSQLCountry;
    fCity: TSQLCity;
  protected
  public
  published
    property Country: TSQLCountry read fCountry write fCountry;
    property City: TSQLCity read fCity write fCity;
  end;

  TSQLRegency = class(TSQLBaseRegion)
  private
    fProvince: TSQLProvince;
    fCity: TSQLCity;
  protected
  public
  published
    property Province: TSQLProvince read fProvince write fProvince;
    property City: TSQLCity read fCity write fCity;
  end;

  TSQLSubDistrict = class(TSQLBaseRegion)
  private
    fRegency: TSQLRegency;
    fCity: TSQLCity;
  protected
  public
  published
    property Regency: TSQLRegency read fRegency write fRegency;
    property City: TSQLCity read fCity write fCity;
  end;

  TSQLVillage = class(TSQLBaseRegion)
  private
    fSubDistrict: TSQLSubDistrict;
    fCity: TSQLCity;
  protected
  public
  published
    property SubDistrict: TSQLSubDistrict read fSubDistrict write fSubDistrict;
    property City: TSQLCity read fCity write fCity;
  end;

  TSQLHamlet = class(TSQLBaseRegion)
  private
    fVillage: TSQLVillage;
  protected
  public
  published
    property Village: TSQLVillage read fVillage write fVillage;
  end;

TSQLAddress = class(TSQLBase)
  private
    fStreetLine1: RawUTF8;
    fStreetLine2: RawUTF8;
    fStreetLine3: RawUTF8;
    fRTRW : RawUTF8;
    fCountry: TSQLCountry;
    fProvince: TSQLProvince;
    fRegency: TSQLRegency;
    fSubDistrict: TSQLSubDistrict;
    fVillage: TSQLVillage;
    fHamlet: TSQLHamlet;
    fZipCode: RawUTF8;
  protected
  public
  published
    property StreetLine1: RawUTF8 read fStreetLine1 write fStreetLine1;
    property StreetLine2: RawUTF8 read fStreetLine2 write fStreetLine2;
    property StreetLine3: RawUTF8 read fStreetLine3 write fStreetLine3;
    property RTRW: RawUTF8 read fRTRW write fRTRW;
    property Country: TSQLCountry read fCountry write fCountry;
    property Province: TSQLProvince read fProvince write fProvince;
    property Regency: TSQLRegency read fRegency write fRegency;
    property SubDistrict: TSQLSubDistrict read fSubDistrict write fSubDistrict;
    property Village: TSQLVillage read fVillage write fVillage;
    property Hamlet: TSQLHamlet read fHamlet write fHamlet;
    property ZipCode: RawUTF8 read fZipCode write fZipCode;
  end;

yes please, where I can send you a more complete version?

coblongpamor · 2012-05-03 18:01:25

ab wrote:

Is it because e.g. the "Country" column does not have an easy way to filter the entry?

yes. i think there are an issue if the alias name same as Name of Field of the Primary table

(SELECT Name FROM Country WHERE ID=Country) AS Country

but, if i change the alias name, e.g CountryName, it's work.
but qoContains sometimes not found matches row, even if with the sftUTF8Text.

mORMot Open Source

#1 2012-05-03 15:43:52

Need Help about SQLite3UIQuery

#2 2012-05-03 15:51:21

Re: Need Help about SQLite3UIQuery

#3 2012-05-03 16:24:47

Re: Need Help about SQLite3UIQuery

#4 2012-05-03 18:01:25

Re: Need Help about SQLite3UIQuery

Board footer