Home » U++ Library support » U++ SQL » How to display fields from different tables in one SQLArray
Re: How to display fields from different tables in one SQLArray [message #19110 is a reply to message #18978] |
Wed, 12 November 2008 22:23 |
|
mirek
Messages: 13976 Registered: November 2005
|
Ultimate Member |
|
|
sergeynikitin wrote on Sun, 02 November 2008 16:09 | By the way, I have decided to how to display related fields from other tables by using SqlArray.
[code]
struct ConvCompany : Convert
{
Value Format(const Value &q) const
{
SQL * Select(COM_NAME).From(COMPANY).Where(COM_ID == q);
String company;
if(SQL.Fetch())
company = SQL[COM_NAME];
else
company = "";
return q.IsNull() ? Null : company;
}
};
|
WARNING: Fetching from SQL ("SQL.Fetch()") is NOT RECOMMENDED (based on expriences:)
It is way too simple to call something in the fetch loop which will issue different SQL statment through SQL.
Use "Sql sql; sql * Select....; sql.Fetch()" instead.
Considering efficiency, IME, this method is usually still passable - one reason is that ArrayCtrl converts only if it needs to Paint it, so if the table has 10000 rows, for the first user action, only about 30 selects are performed.
You can also help it a bit by using "Cache" column modified - in that case, for particular ArrayCtrl cell, Convert is only invoked once, then the converted value is cached. That, obviously, has disadvantages of its own, but usually is harmless.
If you need to be more efficient, you can also use WhenPostQuery or something like that to prefetch all values you need for conversion. E.g. this is direct excerpt from my current job, where I have to solve quite similar problem:
struct NameConvert : Convert {
VectorMap<int, String> name;
VectorMap<int, String> surname;
virtual Value Format(const Value& q) const;
void LoadAttrs();
};
Value NameConvert::Format(const Value& q) const
{
return surname.Get(q, String()) + ' ' + name.Get(q, String());
}
void NameConvert::LoadAttrs()
{
name.Clear();
surname.Clear();
Sql sql;
sql * Select(SUBJECT_ID, ATTR, TEXT).From(SUBJECT_ATTR)
.OrderBy(Descending(Nvl(TILL, Date::High())));
while(sql.Fetch()) {
int sid = sql[SUBJECT_ID];
String id = sql[ATTR];
if(id == "NAME" && name.Find(sid) < 0)
name.Add(sid, sql[TEXT]);
if(id == "LASTNAME" && surname.Find(sid) < 0)
surname.Add(sid, sql[TEXT]);
}
}
Quote: |
Maybe this way: [code]
projects.SetTable(PROJECTV),Leftjoined(COMPANY,VPR_COMPANYID ),AS_PREFIX(VCOM_);
projects.AddColumn(VCOM_COMPANYNAME, t_("Customer"));
This is the ideal, or something similar.
|
I will think about it.
However, 85% of SqlArray code is dealing with insert/update/edit, which would not apply here (IMO). You can easily reimplement those 15% as special case quite easily IMO.
Mirek
|
|
|
|
|
How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
By: zsolt on Mon, 20 October 2008 14:20
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
By: zsolt on Mon, 20 October 2008 22:51
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
By: mirek on Sun, 02 November 2008 16:47
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
By: unodgs on Sun, 02 November 2008 19:09
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
By: unodgs on Sun, 02 November 2008 20:44
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
By: mirek on Sun, 02 November 2008 22:01
|
|
|
Re: How to display fields from different tables in one SQLArray
By: mirek on Sun, 02 November 2008 21:59
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
By: mirek on Wed, 12 November 2008 22:23
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
By: mirek on Sat, 15 November 2008 15:02
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
By: mirek on Sun, 02 November 2008 21:55
|
|
|
Re: How to display fields from different tables in one SQLArray
By: mr_ped on Fri, 20 March 2009 21:37
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
Re: How to display fields from different tables in one SQLArray
|
|
|
template + convert problem
|
|
|
Re: template + convert problem
By: Zbych on Sat, 19 September 2009 19:30
|
|
|
Re: template + convert problem
|
|
|
Re: template + convert problem
|
|
|
Re: template + convert problem
|
|
|
Re: template + convert problem
By: Zbych on Sun, 20 September 2009 15:47
|
|
|
Re: template + convert problem
|
|
|
Re: template + convert problem
|
Goto Forum:
Current Time: Sat May 11 20:05:16 CEST 2024
Total time taken to generate the page: 0.03383 seconds
|