Can't get my head around titleColumn...

[ccrvic]

Hi All.

Sorry for the newbie post, but I want to get this sorted out before my current enthusiasm wanes...

I'm trying to use the titleColumn method to name my records. Unfortunately, the complexity of my database schema exceeds my knowledge...

I've got three tables relevant here - I've got Trip, People, and Booking. Booking is a set of records containing a TripID and a PeopleID, to put people onto a trip.

So what I'm trying to do is to intercept the naming for each given booking, and replace it with the person's name from the People table and the dat from the Trip table. Here's some SQL that does roughly what I want :

SELECT CONCAT(FirstName, ' ', LastName, ' ', StartDate) AS BookingTitle FROM People LEFT JOIN (Booking, Trip) ON People.id = Booking.PersonID AND Trip.id = Booking.TripID; .

So I put that into fields.ini, and my delegate class contains simply:

function titleColumn() {
return "BookingTitle";
}

- I found this approach on the forum after some searching.

Trouble is - it doesn't work. I get an error:

Fatal error: Error retrieving title from database in Dataface_QueryTool::getTitles():SELECT `id`,`BookingTitle` as `__titleColumn__` FROM `Booking`Unknown column 'BookingTitle' in 'field list'

...And at that point, I'm so far out of my depth it's just not funny any more.

Can anyone point me at my error?

Thanks!

Vic.

[shannah]

Hi Vic,

The titleColumn() method can only refer to columns that are included in the current table. Your current definition would then only allow you to use the TripID and PeopleID fields.

This is not insurmountable though. You can "graft" the fields that you need onto the Bookings table by way of the __sql__ directive in the fields.ini file.

At the beginning of your fields.ini file add:

Code: Select all

__sql__ = "SELECT b.*, CONCAT(FirstName, ' ', LastName, ' ', StartDate) AS BookingTitle FROM Booking b left join People p on b.PersonID=p.id LEFT JOIN Trip t  ON t.id = b.TripID"


This SQL query is supposed to replace the default query that is used to load records of the Booking table. It loads all of the normal columns, and adds an additional column "BookingTitle" at the end. You can now reference the BookingTitle field in your fields.ini file like any other field in the Booking table.

Then your titleColumn() method would look like:

Code: Select all

function titleColumn(){
    return 'BookingTitle';
}


You should also implement the getTitle() method as it is used in most places to display a record's title (titleColumn()) is only used in the jump menu.

Code: Select all

function getTitle(&$record){
    return $record->val('BookingTitle');
}


-Steve

[ccrvic]

At the beginning of your fields.ini file add:

Code: Select all

__sql__ = "SELECT b.*, CONCAT(FirstName, ' ', LastName, ' ', StartDate) AS BookingTitle FROM Booking b left join People p on b.PersonID=p.id LEFT JOIN Trip t  ON t.id = b.TripID"


Brilliant. That fixed it.

The "beginning of" is vital - I added it in a section at the end originally, and that failed miserably. There's probably good reason for that - but I'm more worried about being able to use the tool that I am about completely undestanding :-)

Still - my original effort was *almost* corrent - I just missed out the "b.*, " . Considering my complete lack of knowledge about SQL, I'm chuffed with that.

This SQL query is supposed to replace the default query that is used to load records of the Booking table.

Yes - I got that much.

FWIW, I did find it a little counter-intuitive that the two methods in my delegate class - titleColumn() and getTitle() - are so disimillar in their operation. Again, there might be good reason for this, but as a newbie, I found it confusing.

It loads all of the normal columns, and adds an additional column "BookingTitle" at the end. You can now reference the BookingTitle field in your fields.ini file like any other field in the Booking table.

Right. Got it.

You should also implement the getTitle() method as it is used in most places to display a record's title (titleColumn()) is only used in the jump menu.

I'd already done so - but using a mash of mysql_* calls. I think I prefer your version :-)

So - I started using Xataface less than 24 hours ago, and I already have 2 reasonably-complete applications written. I *like* this tool :-)

Vic.