- This topic has 5 replies, 3 voices, and was last updated 3 years, 10 months ago by
.
-
Posts
-
Looking at the code in pdf_creator, it looks like the church directory sort order is determined by the last name of each family, but the sort does not seem to allow for the family where the head of the household is Arnie Doe to be listed before the household of Clyde Doe. It seems to be the luck of the draw.
The relevant line of code seems to be:
sql='SELECT a.*,a.attachment_id AS image, b.* from '.CA_PEO_TBL.' a, '.CA_HOU_TBL.' b where (..privacy=0 OR b.privacy IS NULL) AND a.household_id=b.household_id'; $sql.=$memb_sql; $sql.=$kids_sql; $sql.=' ORDER BY last_name ASC ';In the past, as you were working on this, did you look at this situation?
Thanks.
Yes. I believe you are right except that there is no luck involved. The results should be sorted by the last_name field Ascending and the secondary (implied) sort will be the order the records occur in the table which, in this case, I believe to be the order they were entered.
When Andy Moyle gets a moment he will let us know if we are correct.
I believe you are correct, the implied secondary sort seems to be the order in which the data was entered. This being said, John Doe was entered via the uploaded spreadsheet which I used to initially load the system. While my secretary was verifying the records, she realized that Alfred Doe and his household were not entered initially, so she added them manually. Now John Doe is listed above Alfred Doe, which really doesn’t work for a printed directory.
I’m playing with the household sort order and may need to resort to using two queries. The first query gets the list of head-of-household in alphabetical order sorted by last_name, first_name, middle_initial. This sort also includes the household number. (select last_name,first_name,middle_name,household_id from wp_church_admin_people where head_of_household=1 order by last_name,first_name,middle_name) The second query obtains the adults and then children from the same table grouped by household_id.
I’ve looked at the group by function, but this query doesn’t return the non-head-of-household people (select household_id,first_name,middle_name,last_name,head_of_household from wp_church_admin_people group by household_id order by last_name,first_name,middle_name,head_of_household) I am still looking at group by function to make this into a single query.
This is an interesting bit of work.
I think this query will work…
select a.household_id,last_name,first_name,middle_name,head_of_household,b.address,b.phone from wp_church_admin_people as a, wp_church_admin_household as b where a.household_id in (select household_id from wp_church_admin_people group by household_id order by last_name,first_name,middle_name) and a.household_id=b.household_id order by last_name;
Little confused guys!
The query at the top of this thread isn’t in pdf_creator.php šThe query is for both current address list pdf versions in v2.72220
SELECT a.household_id,b.privacy,b.attachment_id AS household_image FROM '.CA_PEO_TBL.' a LEFT JOIN '.CA_HOU_TBL.' b on a.household_id=b.household_id WHERE (b.privacy=0 OR b.privacy iS NULL) '.$memb_sql.' GROUP BY a.household_id ORDER BY a.last_name ASCThat current query will order by last name and then indeed in a secondary way by the household table index – household_id (which will be the order they were entered)
It fails on my MAMP install PHPMyAdmin, but “works” in the plugin š Figure that
Some of the issues involved….
- Newer versions of MySQL freak out with GROUP BY and ORDER BY under certain circumstances, so can’t use that. Above query on my home server now gives error
#1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘together_gateway.a.last_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by - Need to do it with head_of_household=1 as some households will have different last names (even married couples – eg in Polish the last name word ending changes by gender, and many Indian families often the wife has the husbands first name as their surname etc.)
- Need to have privacy=0 in the query so that people who don’t want to be shown aren’t shown
- Speed will be a factor – some of the churches using the plugin have 1,000s in their address list
I’ve come up with
SELECT DISTINCT a.household_id,a.*,b.privacy,b.attachment_id AS household_image FROM '.CA_PEO_TBL.' a LEFT JOIN '.CA_HOU_TBL.' b on a.household_id=b.household_id WHERE (b.privacy=0 OR b.privacy iS NULL) AND a.head_of_household=1 '.$memb_sql.' ORDER BY a.last_name,a.first_name,a.middle_name ASC- Privacy maintained
- The GROUP BY error is avoided by using DISTINCT on a.household_id
- The query only looks at heads of household
- ORDER BY solved the issue and Selecting a.* stops an error where the ORDER BY is not included in the query.
I’ve tested it with 2 households created the wrong way round alphabetically in the table and it seems to work!
Can you guys test and get back to me just to make sure please.By the way I have define(‘CA_DEBUG’,TRUE); in my wp-config.php for debugging to wp-content/uploads/church-admin-cache/debug_log.php
- Newer versions of MySQL freak out with GROUP BY and ORDER BY under certain circumstances, so can’t use that. Above query on my home server now gives error
- You must be logged in to reply to this topic.
