Handling different lookup tables by class

I ran into this once in the early days of PIRETS 3, but wrote it off as just a fluke of an individual funky RETS provider. I'm quite disheartened to see that's not the case.

The problem with just appending the values to the allowed values of the CCK field is that we'll have several computer-friendly values for the same human-readable value. For example, if the field represents roofing style, there will be a "Shingle" for RE_1, a "Shingle" for CI_2, etc, each with different computer-friendly values. If all we care about is displaying listings, this isn't a problem, but things get ugly quickly when it comes to searching listings. If we have a drop-down menu field which shows all possible values for that field, "Shingle" (and other values) will be listed several times. And if we try to search for RE_1 listings with the "Shingle" value for CI_2 listings, zero listings will be returned…

(takes off glasses, closes eyes, rubs bridge of nose between fingers, exhales deeply)

Well, I thought up a solution which might be feasible. We add another table (sigh) named something like pirets_lookups_by_class which contains these fields: system_name, class, lookup, lookup_multi. The latter two fields are removed from the pirets_fields table.

Instead of storing possible values as "computer_val|Human friendly value," CCK fields will just store the human friendly values. So that way "Shingle" can have the same value across all classes. This seems destructive to the data from RETS - how do we get the computer value if we're just storing the human friendly value? Well, we just have to use some idiotic query like: SELECT pl.value FROM pirets_lookups pl INNER JOIN pirets_lookups_by_class plbc ON pl.lid = plbc.lookup WHERE pl.long_value = 'Shingle' AND plbc.class = 'RE_1'

Thinking of writing the hook_update_N() for this is making me lose bladder control. And this all falls apart if the Roofing lookup for RE_1 has "Shingle" and the one for CI_2 has "Shingles" and the one for MF_3 has "Shingel" and the one for BL_4 has "shingle."

If we want to use a select list/menu for the exposed filter in question, is there another way to do it? I suppose I could define my own CCK field and/or Views filter, but that's not really what PIRETS is about. Maybe I don't understand your question…

Did some initial work on this. I'm probably going to just give up on coming up with an elegant upgrade path for this and instead just tell everyone to update their fields and listings manually…

I spent a whole lot of time on this yesterday, but eventually I stopped because I really did not like the mess the code was turning in to. It was a learning experience, though. I ended up committing what I had so far, then reverting back to the pre-mess changeset.

I definitely plan on taking another swing at this eventually, particularly now that I have a clearer idea of the magnitude of the problem. However, I'm starting to think that, like #726392: Multiple Server settings and connections and Resource Type, this is an issue which is going to have to be tackled through a major rewrite of PIRETS' connection code rather than tweaking what's there now.

so what can I do when fields are showing the values (41) rather than the lookup long-values (finished)? just noticed after moving to yet another server...

Nothing, at the moment. However, it appears that a client may be fronting the money for us to put int he time to get this working, though I'm not entirely sure how yet - whether it can be done in PIRETS 3 without too much trouble, or if we're essentially talking about building PIRETS 4 here. We should have a clearer picture of things come the weekend.

looking into the database, the cck fields are populated with all lookup ids in one row (i.e. 3739404217)
if I go and edit the property it will split those values into separate rows, see attachment

so seems like the updates are missing a for each loop or something...?

Grr… I bet that's due to a conflict between code to remove commas from certain fields (due to Interealty sticking commas in fields where they don't belong) and multiple-value lookup fields, which will legitimately have commas in the field to separate values. Dumb mistake, but also surprised nobody's caught it before…

When the next dev release becomes available, please give that a try; flushing and refetching the affected listings.

Thanks Garrett, Sept.21 dev got my multi-value lookups displaying properly again. Looking forward to Pirets 4 : )

This is still an issue for me with FlexMLS using the latest PIRETS dev release (Dec 30 2010). It appears the PIRETS is only getting the allowed values for the first property class. I created a duplicate issue here, please feel free to close it, (sorry about that).

Posted by camidoo on March 9, 2010 at 11:14am
Anyone else happen to have any fields shared across classes that have different lookup tables? or at least different lookup table names?

And to answer your question, yes, I do! FlexMLS does this too, and it's still an issue with the current PIRETS dev release. The lookup values are showing for every class besides the first one (A/Residential).

Posted by camidoo on March 9, 2010 at 11:14am
the big shift was to go from requesting RETS data as COMPACT to COMPACT-DECODED

Also worth noting that this solves nothing currently with PIRETS. I tried adding ?Format=COMPACT-DECODED to my Search URL. It results in the multiple select field blanking issue, which occurs when editing a PIRETS node and saving it. Since PIRETS populates the "allowed values" for the CCK multi-select field, and since PIRETS inserts the COMPACT-DECODED value (which is not an allowed value), it is blanked when the node is saved manually a second time. Any ideas how to workaround this? It seems like I need to add a loop for whichever function is determining all possible values, and force it to fetch values for each class before populating the content type allowed values.

Posted by webavant on February 2, 2011 at 8:55am
It seems like I need to add a loop for whichever function is determining all possible values, and force it to fetch values for each class before populating the content type allowed values.

Hmm, so it looks like you're already doing that in pirets.connect.inc, but for whatever reason, it only gets the data for residential class on FlexMLS.

So I printed the contents of $rets_fields[$field['SystemName']] during the loop in pirets_fields_fetch() from pirets.batch.inc. Shouldn't the 'classes' array contain all of the classes and not just one? See below:

Array
(
    [system_name] => LIST_39
    [cck] => pirets_LIST_39
    [data_type] => Character
    [max_len] => 50
    [prec] => 0
    [long_name] => City
    [classes] => Array
        (
            [0] => A
        )

    [lookup] => 20050816162041367239000000
    [correlation] => loc_city
    [lup_multi] => 0
)

I am interested in seeing a resolution to this issue. Does that entail rewriting PIRETS as you previously suggested? I don't know what your interest level in it is, but if you created a project on an "all-or-nothing" style funding platform, I would definitely pledge a portion to help foot the bill.

The truth is I haven't worked with PIRETS in a while, but taking a glance at the commit history, it appears that what happened is that the code I wrote to support this was in a specialized branch for a particular client. I thought I had merged it into the mainline branch, but it appears I didn't. Anyway, if you're adventurous, you can access this code in the BitBucket repository - it's the 3.1 branch, which is a little confusingly-named.

The other truth is that I no longer work for PI full-time anymore, instead more as a consultant/freelancer when they need something done that only I would know best (like PIRETS). And I'm really not in love with RETS enough to work on PIRETS in my free time… So, yeah, it may help to contact PI and get a contract. Then PI can come to me with a little financial incentive to get it done (or possibly one of their in-house programmers, though I'm not sure how well they know PIRETS yet).