Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » How to display fields from different tables in one SQLArray
How to display fields from different tables in one SQLArray [message #18765] Mon, 20 October 2008 14:14 Go to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

How to display fields from different tables in one SQLArray.
Are there any analog View-Join

There are 2 tables:

TABLE_(PROJECT)
INT_ (PR_ID) PRIMARY_KEY AUTO_INCREMENT
INT_ (PR_CREATIONDATE)
INT_ (PR_USERID)
INT_ (PR_COMPANYID)
STRING_ (PR_TASKTODO,2000)
DOUBLE_ (PR_SUM)
INT_ (PR_PLANNEDDELIVERYDATE)
DOUBLE_ (PR_SUMFACT)
INT_ (PR_STATUS)
INT_ (PR_PAYSTATUS)
INT_ (PR_PLANNEDEXECUTIONTIME)
STRING_ (PR_COMMENT,2000)
END_TABLE

and

TABLE_(COMPANY)
INT_ (COM_ID) PRIMARY_KEY AUTO_INCREMENT
INT_ (COM_GROUPID)
STRING_ (COM_NAME,2000)
STRING_ (COM_INN,2000)
STRING_ (COM_KPP,2000)
STRING_ (COM_ADDRESSDOC,2000)
STRING_ (COM_ADDRESSFACT,2000)
STRING_ (COM_COMMENT,2000)
END_TABLE

They relate by field PR_COMPANYID

I need to display the first table, and instead of field PR_COMPANYID I need to display COM_NAME from second table. I found 2 solutions. First - do Join View on the SQL server. Second - do converter to display the column in the table. I think that there should be a 3rd - Correct.

Please Give the right decision.


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: How to display fields from different tables in one SQLArray [message #18766 is a reply to message #18765] Mon, 20 October 2008 14:20 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
I use simple ArrayCtrl to show this type of query results.

See SqlArray source to find out, how to create a select, based on ArrayCtrl columns.
Re: How to display fields from different tables in one SQLArray [message #18769 is a reply to message #18766] Mon, 20 October 2008 14:28 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

Could you give simple example?

SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: How to display fields from different tables in one SQLArray [message #18771 is a reply to message #18769] Mon, 20 October 2008 14:55 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

Maybe there is method to achieve the same result using SqlArray?

SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: How to display fields from different tables in one SQLArray [message #18785 is a reply to message #18769] Mon, 20 October 2008 22:51 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
First you have to add the fields to your arrayctrl:
m_array.AddKey(PR_ID); //primary key
m_array.AddColumn(PR_CREATIONDATE, t_("Creation Date"), 10)
    .SetConvert(Single<ConvertYourIntBasedDate>());
...
m_array.AddColumn(COM_NAME, t_("Company Name"), 10);
...


You have to have a function returning the fields of your Select statement:
SqlSet GetSqlColumns(const ArrayCtrl &a_array)
{
	SqlSet cols;
	for(int i = 0; i < a_array.GetIndexCount(); i++)
		if(!a_array.GetId(i).IsNull())
			cols.Cat(SqlId(a_array.GetId(i)));
	return cols;
}


And a code to fill the arrayctrl from db:
SqlSet cols = GetSqlColumns(m_array);
Sql sql;
sql * ::Select(Distinct(cols))
	.From(PROJECT)
	.LeftJoin(COMPANY)
	.On(COM_ID == PR_COMPANYID)
	.Where(1)
	.OrderBy(PR_ID)
;

for(;;) {
	Vector<Value> row;
	if(!sql.Fetch(row)) break;
	m_array.Add(row);
}

Re: How to display fields from different tables in one SQLArray [message #18786 is a reply to message #18785] Mon, 20 October 2008 23:00 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

Thanks

SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: How to display fields from different tables in one SQLArray [message #18787 is a reply to message #18786] Mon, 20 October 2008 23:04 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

Does works methods like like Query(), Requery().???

SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: How to display fields from different tables in one SQLArray [message #18949 is a reply to message #18787] Sun, 02 November 2008 14:12 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

Maybe I need GridControl ?

Can GridControl show in different columns data from related tables (child-parent)?

How to do this??


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: How to display fields from different tables in one SQLArray [message #18963 is a reply to message #18787] Sun, 02 November 2008 16:47 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
sergeynikitin wrote on Mon, 20 October 2008 17:04

Does works methods like like Query(), Requery().???


No. You have to implement them yourself. But it is quite simple.

Mirek
Re: How to display fields from different tables in one SQLArray [message #18964 is a reply to message #18963] Sun, 02 November 2008 16:54 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

I have a huge table 50000-100000 records. It involves 12-15 reference tables. This is stored in the MySQL server.

What kind of technology would you recommend to work with the database.

Will all the data loaded into memory or the client program will be loaded only a fraction?


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: How to display fields from different tables in one SQLArray [message #18965 is a reply to message #18964] Sun, 02 November 2008 19:09 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

sergeynikitin wrote on Sun, 02 November 2008 10:54

I have a huge table 50000-100000 records. It involves 12-15 reference tables. This is stored in the MySQL server.

What kind of technology would you recommend to work with the database.

Will all the data loaded into memory or the client program will be loaded only a fraction?


Loading it all would make your app working very slow. In my current app (Postgres) I use offset limit to page data. And of course I implemented special toolbar for navigating:
index.php?t=getfile&id=1464&private=0
In SqlServer you can use OVER and ROW_NUMBER() to do the same.
  • Attachment: nav.png
    (Size: 1.86KB, Downloaded 1386 times)
Re: How to display fields from different tables in one SQLArray [message #18966 is a reply to message #18965] Sun, 02 November 2008 19:41 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

That is, in short, GridCtrl download the entire table in memory of the client, and does not limit the recording while watching?

And is there any way to avoid this unpleasant fact and load only those records that are visible on the screen, and then as we move curdor row on the table load on one record, or any small portions?

Maybe there is a method or a callback, in which I can do this functionality to the SqlArray.


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: How to display fields from different tables in one SQLArray [message #18969 is a reply to message #18966] Sun, 02 November 2008 20:44 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

No, GridCtrl doesn't download entire table into memory. In fact it dosn't load anything itself Wink. Just told you how I deal with large tables. When user press second page I load only that page into memory. Numbers of page rows is also determined by user.
Dynamic loading during scrolling is imo a bad idea. Your ui actions will be "delayed" while loading next portion of rows. Of course you can load data when scrollbar's thumb stops moving, but it implies the next problem - what will you show to the user during scrolling action?
Re: How to display fields from different tables in one SQLArray [message #18973 is a reply to message #18969] Sun, 02 November 2008 21:50 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

unodgs wrote on Sun, 02 November 2008 22:44

Of course you can load data when scrollbar's thumb stops moving, but it implies the next problem - what will you show to the user during scrolling action?


If you say about scrollbar position than it's no problem. We are know quantity of rows in whole table and we can calculate rihgt position of sb.

And on the position of SB, we can calculate the position in the table to display.

Of course it is, I suppose. Ideally.



SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: How to display fields from different tables in one SQLArray [message #18974 is a reply to message #18964] Sun, 02 November 2008 21:55 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
sergeynikitin wrote on Sun, 02 November 2008 10:54

I have a huge table 50000-100000 records. It involves 12-15 reference tables. This is stored in the MySQL server.

What kind of technology would you recommend to work with the database.

Will all the data loaded into memory or the client program will be loaded only a fraction?



Just a funny note: I have just tried what it takes to fetch 130000 records to ArrayCtrl.

It is about 400MB.

Therefore, I would recommend trying the simple path first - do not worry and try it. Maybe the table is not as huge as you think.

You know it, "premature optimization..."

(Well, it definitely is not a huge table. The biggest table I am regulary dealing with has 2 500 000 records Smile

Mirek

[Updated on: Sun, 02 November 2008 21:56]

Report message to a moderator

Re: How to display fields from different tables in one SQLArray [message #18975 is a reply to message #18973] Sun, 02 November 2008 21:58 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

There is a proprietary product TopSpeed Clarion.

When I first tried IDE Ultimate + +. I saw a lot of similarities with the Clarion.

In Clarion just used this approach to display the huge tables (but not huge, too), when the client receives from the server only lines that appear on the screen at the moment.

Of course it is difficult to program, but it works quite quickly.


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: How to display fields from different tables in one SQLArray [message #18976 is a reply to message #18969] Sun, 02 November 2008 21:59 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
unodgs wrote on Sun, 02 November 2008 14:44


Of course you can load data when scrollbar's thumb stops moving, but it implies the next problem - what will you show to the user during scrolling action?



Actually, shadow areas that get updated as soon as rows get fetched in would not look that bad Smile (I am using similar principle in RichEdit when rescaling window with images).

Mirek
Re: How to display fields from different tables in one SQLArray [message #18977 is a reply to message #18975] Sun, 02 November 2008 22:01 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
sergeynikitin wrote on Sun, 02 November 2008 15:58

There is a proprietary product TopSpeed Clarion.

When I first tried IDE Ultimate + +. I saw a lot of similarities with the Clarion.

In Clarion just used this approach to display the huge tables (but not huge, too), when the client receives from the server only lines that appear on the screen at the moment.

Of course it is difficult to program, but it works quite quickly.


IMO, the problem is that there is no generic way how to create random access cursor to all suported RDMBs... AFAIK.

OTOH, maybe this is something to investigate.

Mirek
Re: How to display fields from different tables in one SQLArray [message #18978 is a reply to message #18976] Sun, 02 November 2008 22:09 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

By the way, I have decided to how to display related fields from other tables by using SqlArray.

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;
        }
};

.....
.....

projects.AddColumn(VPR_COMPANYID, t_("Customer")).SetConvert(Single<ConvCompany>());


This method is bad because they occur too frequent recourse to the server, but the program looks beautiful.


It would be good to do the same method could display LEFT JOINED FIELD.

Maybe this way:
projects.SetTable(PROJECTV),Leftjoined(COMPANY,VPR_COMPANYID),AS_PREFIX(VCOM_);
projects.AddColumn(VCOM_COMPANYNAME, t_("Customer"));


This is the ideal, or something similar.



SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}

[Updated on: Sun, 02 November 2008 22:19]

Report message to a moderator

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 Go to previous messageGo to previous message
mirek is currently offline  mirek
Messages: 13975
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
Previous Topic: DBF crash error while writing record
Next Topic: PATCH/BUGFIX Oracle asTable Failure - Solution
Goto Forum:
  


Current Time: Thu Mar 28 10:40:12 CET 2024

Total time taken to generate the page: 0.01662 seconds