Generate poster board number based on speaker/presenter last name

1

Hi,

I am managing a conference with about 400 poster contributions.

The chair of the conference wishes to order the poster boards according to the last name of the poster presenter.

I feel that it should be possible to automate this via indico shell since there is Contribution.board_number in the API.

So I can make the query for posters

event = Event.get_one(<event_id>)
posterQuery = Contribution.query.with_parent(event).filter(Contribution.type_id == <poster_contrib_type_id>)

And I can sort by say, the poster title

posterQuery.order_by(Contribution.title).all()

But I cannot sort by the last name of the speaker since this would give a list of speakers (even though we only have one speaker per contribution in our event, in principle there can be multiple speakers). The actual error is: SQL expression object or string expected, got object of type <type ‘property’> instead.

posterQuery.order_by(Contribution.speakers).all()  # does not work
Contribution.get_one(<id>).speakers[0].last_name  # is what I want to sort by

After some discussion with others who know more about SQL, it seems that I need to perform a join of some kind. I tried then

posterQuery.join(ContributionPersonLink)

which seems to work. But what I found confusing was

posterQuery.count()  # 416 
posterQuery.join(ContributionPersonLink).count() # 2217

I thought the join would only take the contributions that are posters and do an inner join, but that doesn’t seem to be the case. So then I tried:

posterQuery.join(ContributionPersonLink).filter(ContributionPersonLink.is_speaker).count() # 416

which seems to generate the right count. Then I tried to sort this by last name:

PosterQuery.join(ContributionPersonLink).filter(ContributionPersonLink.is_speaker).order_by(ContributionPersonLink.last_name)

This time I am able to get an output. But what looks weird is when I check the speakers:

(what I use for checking the output)

testList =  PosterQuery.join(ContributionPersonLink).filter(ContributionPersonLink.is_speaker).order_by(ContributionPersonLink.last_name).all()
for contrib in testList :
    cid = contrib.id
    contribspeakers = contrib.get_one(cid).speakers
    print contribspeakers

It seems to me that the first 40 entries were sorted according to the last name of the speakers, but the rest are not. I am rather confused about why.

To return to the task I wanted to do, I am now going to do it through more Python rather than getting the sorted list directly from the database. But I would be interested to know if there is a better way of doing it (or better way of doing any of the steps along the way).

Thanks,
Ying

2

Here is what I ended up doing. Feedback welcome!

event = Event.get_one(<event_id>)
posterQuery = Contribution.query.with_parent(event).filter(Contribution.type_id == <poster_contrib_type_id>)
posterList = posterQuery.all()


# check posters only have one speaker

for poster in posterList: 
    if len(poster.speakers) > 1 :
        print poster
        print 'has more than one speaker'
        print ''

posterSpeakersDict = {}

for poster in posterList :
    speakerLastName = poster.speakers[0].last_name.strip()
    posterSpeakersDict[poster.id] = speakerLastName

# Some people have leading spaces in their last names - now fixed with strip().

# check length of dict. Cannot use last name as key since can have duplicate last names.
len(posterSpeakersDict)

# testing sort output
sorted((value,key) for (key,value) in posterSpeakersDict.items())

# list with just poster IDs
sortedPosterIDList = sorted(posterSpeakersDict, key = posterSpeakersDict.get) 

# loop to assign board numbers

boardNum = 1

for pid in sortedPosterIDList :
    #print pid, 'Board #', boardNum
    Contribution.get_one(pid).board_number = unicode(boardNum)
    boardNum = boardNum + 1

db.session.commit()