How can this be fixed to only search for the first letter?
Custom SQL really falls outside the scope of support we provide here in the forums, but I can offer some advice to help you find your own custom solution. It looks like your posts_where_first_letter filter code is attempting to directly manipulate the SQL query to replace a specific string with a different string. However, the "source" string (i.e. $where) is not identical to the string produced by the SQL query, so no replacement is happening. To analyze the actual SQL query string, go to Toolset > Settings > Front-end Content and turn on " Enable Views debug mode" and choose the full debug mode. Reload the page hidden link and a popup will be shown (you may need to disable your popup blocker to see this). In the popup, you will be able to find the MySQL query generated for each View on the page. Look for the MySQL query generated for the View 992, and you will see something like this:
SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND wp_posts.ID NOT IN (981) AND (
wp_postmeta.meta_key = 'wpcf-wissenschaftler-nachname'
AND
(
( mt1.meta_key = 'wpcf-wissenschaftler-nachname' AND mt1.meta_value LIKE '{a4eb99cc5d88943e53669597c5fc7ada6b1c13244adec4dc3ff3e436b08f1b90}D{a4eb99cc5d88943e53669597c5fc7ada6b1c13244adec4dc3ff3e436b08f1b90}' )
)
) AND wp_posts.post_type = 'wissenschaftlerinnen' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC, wp_posts.post_title DESC
The problem in this query is here:
( mt1.meta_key = 'wpcf-wissenschaftler-nachname' AND mt1.meta_value LIKE '{a4eb99cc5d88943e53669597c5fc7ada6b1c13244adec4dc3ff3e436b08f1b90}D{a4eb99cc5d88943e53669597c5fc7ada6b1c13244adec4dc3ff3e436b08f1b90}' )
Instead you want to test only the first letter, so you need something like this:
( mt1.meta_key = 'wpcf-wissenschaftler-nachname' AND mt1.meta_value LIKE 'D{a4eb99cc5d88943e53669597c5fc7ada6b1c13244adec4dc3ff3e436b08f1b90}' )
That effectively removes the first '%' in the query. The long string {...} represents a % symbol, and it is not static.
If you look in your PHP filter code, you can see that the string replacement "search" text is searching for text that does not exist in the actual MySQL query. For example, "CAST AS" does not exist in the actual SQL query string. So your search text must be adjusted to compensate for the differences in the source text and the replacement text. The hash value that represents '%' here will be different each time the query is submitted, so you cannot consider that hash to be static. If you are skilled with Regular Expressions, this should be pretty straightforward for you. I am not very skilled with Regular Expressions, so I don't have a simple cut-and-paste solution for you. Instead, I suggest you use PHP's strpos method to find the position of the first instance of AND mt1.meta_value LIKE, then find the position of the next instance of }. Knowing those two string positions would give you the ability to strip out the first hash in the query string, which would change the query to search only the first letter.
https://www.php.net/manual/en/function.strpos.php
I'm happy to help troubleshoot your code updates if you try to use strpos and it's not working as expected, just paste your progress and we can review together.
Aditionally: Can the filter be adjusted to always show all first letters? Now at hidden link only "Alle" and "B" is shown.
Can you explain why there is a custom field Query Filter in the View here?
hidden link
It seems that you could remove this Query Filter, and the list of first letters would not be filtered using the URL parameter.
