How do I change the sql query for wp_get_archive()

Looking at the source code for wp_get_archives(), there is a filter called getarchives_join which you can use to restrict wp_get_archives() to a specific category

You also need to get the current category ID, which is easy, simply use get_queried_object_id()

You can try something like this to display archives for the currently viewed category

add_filter( 'getarchives_join', function ($r) 
{

    global $wpdb;

    if( is_category() ) {
        $category_id = get_queried_object_id();
        $r =  $wpdb->prepare( " INNER JOIN $wpdb->term_relationships AS tr ON $wpdb->posts.ID = tr.object_id  AND tr.term_taxonomy_id IN (%s)", $category_id );
    }
    return $r;

}, 10, 2 );

EDIT

Just one or two comments about your code in your last edit. As that is the code in category.php and archive.php templates, you should not make use of custom queries. I suspect that this is where you big problem is.

Use pre_get_posts to alter the main query to suit your needs. You have to remove the custom queries and go back to the default loop in those two archive pages.

This is how your category page should look like

<?php get_header(); ?>

<?php 
if( have_posts() ) {

    while ( have_posts() ) { 
        the_post();

        the_content();

    }

}
?>

<?php wp_get_archives( array( 'type' => 'daily') ); ?>

Then, in your functions.php, addd the following code

add_action( 'pre_get_posts', function ( $q ) 
{

    if(    !is_admin() 
        && $q->is_main_query() 
        && (    $q->is_category() 
             || $q->is_date() 
            ) 
    ) {
        $q->set( 'posts_per_page', 1 );
    }
});

This will set the posts per page to one on the archive and category pages

EDIT

Related Posts: