Quick question. I’m using BKeeney ActiveRecord in a db project. I know that it currently doesn’t support related records. My question is: what’s the best way to display a list with data from 2 (or more) related tables?
Example:
table 1: client (id,name, countryID)
table 2: country (id,name)
Using ActiveRecord, I would like to display a list with:
client.name - country.name
what’s the best way without having a query to the db for every row that gets displayed to find the country name?
Thanks
Hello Bruno,
yesterday i’ve loaded and played with ActiveRecord from Bob’s Homepage the first time. Have you downloaded his example on his Homepage? There he uses a one to many relation with two object lists (person one) to (contact many )
You can find an enhanced SharedMethod ListbyPersonID in the Contact Class. This is used from the Person Class and only shows the related records of Person an Contact for the relation.
i think there are two ways of doing it:
A) create an extra Property of class country in the client class (only works in 1 to 1 relations; in one to many you have to create a array of the country class). As i understood this property can be easily filled with related Data by the special List Method (SharedMethod ListbyPersonID)
B) when you go iterate through the first list object (client) you can search for the related record in the country table using the load method of the Country object to show the land information.
I think displaying a list with relations is not the main thing for which ActiveRecord is made. I think the advantage is easy manipulating data in the main tables in the database. Another Problem of the list object could be perhaps in larger Databases, because for every Record in the table there has to be created an object of the class. With smaller Databases no problem i think.
In my new project i think i ail mix it up. I will show lists of Data generated by PreparedSQLStatements and then picking up a record of the list an create a ActiveRecord class object of the specific subclass.
But i have another question because i started with ActiveRecord yesterday. Which Database do you use? I’ve an issue with PostgreSQL. So I think you are using SQLite?
greetings from Germany
Björn
nice to find someone using ActiveRecord too 
What we do in situations like this where the records don’t change is use a SharedMethod in the Country data class that when it’s accessed for the first time, loads the data into a dictionary and then subsequent calls simply get the value out of the dictionary.
The drawback is that it’s always in memory but it is very quick and after the first access there are no more queries.
Make sense?
@Björn Dohle :
I did download the person/contact example. It makes sense and works great when you click on one item and then display a list of related records. My question was for a single list aggregating data from several tables: can we do it without a separate hit to the db for each line of the list? Don’t think so.
I’m using Postgres as the db and I had to do some minor tweaking. It did not work straight “out of the box”. I don’t remember all that I did, but I remember struggling with a few things. One is to make sure all your table names and field names in the postgres db are all lowercase. I also had to make a change to the Postgres connector to make the delete method work by changing the placeholder from “?” to “$1” in the prepared statement. Once I had corrected these and some other little things I can’t remember, it all worked perfectly. It is stable and very complete. I wish some more doc would exist for other stuff (like the list class) because AR is definitely packed with a lot of time saving power!
@Bob Keeney :
A shared method does make sense if the related table only contains a limited set of different data (like the country example I used). It makes less sense when the amount is larger. I did think about different mechanisms where I could cache the data and update after some period of time or if the most recent modification date of all records is different from the db. The out-of-sync situation is of some concern as the related data will change periodically.
One thing I did in the past for situations like this was to create a view in the db engine. This way only one access to the db is made to get the data. I tried it with ActiveRecord but since views are not tables, the view doesn’t get recognized so my work around would require some major changes in AR to support not only tables, but also views.
Anyhow, after discussing with one of my colleagues who worked in a much larger and complex db environment, it seems hitting the db with 50 requests to display a list might not be that problematic. I’ll have to test it to see.
Thanks to both of you!
Hello Bruno,
nice to read that you are also using PostgreSQL as Database. In my first attempts i was wondering why some methods like load and delete have thrown Exceptions. It was because of the “?” instead of “S1” as placeholder in the prepared statement, as you just said. I thought that ActiveRecord is really a Plug and Play solution for all supported DB out of the Box, but you have to edit some things to get it work for specific DB as i understand at the moment. Not so much but without a good documentation… 
I think Bob should make it a payable Product. ARGEN with ActiveRecord in one Package. The Basic Functions ActiveRecord Folder perhaps should also be generated in the ARGEN process and it should work out of the Box for all supported DB. ActiveRecord HAS A LOT of Power!!!
But I think it would be not so much work to cast the DB in this methods and make it really a out of the Box solution. Like said, i’ve tested just for a day a bit, but like Bob always said, i think ActiveRecord could be a very powerful tool for rapid application development with DB support.
One Idea not tested, when the List Helper object is created, every single class object is created? There is a “AfterCreate” EventDefinition in the Base Class. Perhaps you can place the code there which is in the Click Event of the Main List? to get what you want? A Relation object in the main object client class.
It would be nice if other users could post their experiences with ActiveRecord an Databases here too.
Another question Prepared Statements are allowed in which Databases with original XOJO Plugins?
I know:
REALSQLDatabase
SQLite
PostgreSQL
don’t know:
MicrosoftSQL Server
mySQL
Would be nice to stay in contact here or outside via email
Björn
+1 for a paid version with better doc.
As for prepared statement, I’m not a db expert but I think they are supported by all Xojo db plugins. MySQL is supported for sure. I’ve used it and ActiveRecord supports it too.
To keep in touch, I’ll send you my email private.
Cheers!
@Bruno: did you report back all the required changes to Bob?
No I did not. I guess it would be a good idea to do it! I believe that the main thing is to change all placeholders from “?” to “$1 $2 $3 etc”. I’ll be at the XDC in Vegas so I’ll tell him directly there
Thanks!
Hmm. I don’t know anything about the ActiveRecord stuff, but I surely wouldn’t want to use a framework that would force me to do that. Getting related data is usually just a matter of RecordSet=MyDB.SQLSelect(“Select * from ManyTable where IDOfOneTable=SomeID”). The ActiveRecord code should offer an option to not use the cached data.
It doesn’t matter much performance wise if you use prepared statements, btw.
I’d be surprised if 50 round trips to the DB don’t create a performance problem.
ActiveRecord doesn’t fit every need and you can still do your own query and data handling for lists where it cannot help. It does not take over everything. That’s probably what I’ll end up doing: make manual queries for some lists and use ActiveRecord for the rest.
I also think that 50 round trips will have an impact, but again, maybe not that much. I’ll see!
I use AR (ActiverRecord) for almost all my dbase related stuff. It makes it much easier for me (non-dba) to manage the database and when there are changes the app wont compile unless I make the appropriate changes to it.
Now I understand that there is not an AR object that is a mesh of record from “Table A” and a record from “Table B”. Depending on what is in the two tables (size of each record, number of records) and how much you need the various fields from each. You have options.
from your example, you could pull in the list of the countries into an object array, then process the clients like you normally would. but when you need the country name, use/reference the object in the object array. Not the cleanest/easist way to do it.
the other way is use RecordSets and do manual SQLSelects on the database across the tables.
good luck.
Thanks Scott. It think these are the 2 only options.
And yes, ActiveRecord has so many pros it’s a small price to pay to have to program a few workarounds for the things it can’t do.
See you at XDC!
[quote=71243:@Bruno Frechette]Thanks Scott. It think these are the 2 only options.
And yes, ActiveRecord has so many pros it’s a small price to pay to have to program a few workarounds for the things it can’t do.
See you at XDC![/quote]
you welcome. and I wont be at XDC this year.