#StackBounty: #php #wp-admin #mysql #meta-query #sort Sortable admin column for one meta key with three possible meta values

Bounty: 50

I’ve looked over 2 or 3 similar looking threads regarding sortable columns and the meta queries involved with making them work. None seem to resolve my exact problem.

I am using the pre_get_posts filter to hook the sortable logic for an admin column. I have a post meta checkbox that I’d like to sort, it’s key is _prioritize_s.

When checked, the value is 'yes'. If it has never been checked, then neither the key nor value exists. If it’s been checked before, and then unchecked, the key exists, but the value is an empty string.

Here is how I’d like it to order:

  • meta key _prioritize_s = ‘yes’, from the latest to oldest post
  • meta key _prioritize_s = empty string OR NOT EXISTS, from the latest to oldest post

Here is what I have. It kind of works, but the date order doesn’t seem to work properly and I need to group the NOT EXISTS query with the one where the value could be an empty string?

class Featured_Admin_column {

    public $column_names = [
        'prioritized_post' => '_prioritize_s',
    ];

    function init() {
        $this->hooks();
    }

    function hooks() {
        add_filter( 'manage_edit-post_sortable_columns', [ $this, 'create_sortable_columns' ] );
        add_action( 'pre_get_posts', [ $this, 'set_meta_for_sortable_columns' ] );

    }

    function create_sortable_columns( $columns ) {
        $columns['prioritized_post'] = 'priority';

        return $columns;
    }

    function set_meta_for_sortable_columns( $query ) {
        if ( ! is_admin() ) {
            return;
        }

        $orderby = $query->get( 'orderby' );
        if ( 'priority' == $orderby ) {
            $query->set( 'meta_query', array(
                'relation' => 'OR',
                array(
                    'key'   => '_prioritize_s',
                    'value' => 'yes',
                ),
                //The 2 arrays below should be grouped together somehow...
                array(
                    'key'   => '_prioritize_s',
                    'value' => '',
                ),
                array(
                    'key'     => '_prioritize_s',
                    'compare' => 'NOT EXISTS'
                )
            ) );

            $query->set( 'orderby', 'meta_value date' );
        }
    }

Update: I’ve tried messing around with this some more, and thinking maybe WP considers blank values as null as well. When I tried putting the meta query on a page template so I could echo out things and test before I mess with the admin columns, I tried reducing the meta query for 2 arrays instead of 3. I get both posts with and without the post meta _prioritize_s but the date order is still out of whack despite using an array in the orderby. What am I missing?

$test_sorting_by_priority = get_posts( [
    'posts_per_page' => - 1,
    'order'          => 'DESC',
    'orderby' => array(
        'exists' => 'date',
        'empty' => 'date',
    ),
    'meta_query'     => array(
        'relation' => 'OR',
        'exists'   => array(
            'key'     => '_prioritize_s',
            'value'   => 'yes',
            'compare' => 'EXISTS',
        ),
        'empty'    => array(
            'key'     => '_prioritize_s',
            'compare' => 'NOT EXISTS'
        ),
    ),
] );

foreach ( $test_sorting_by_priority as $test ) {
    $empty_or_null = isset( $test->{'_prioritize_s'}) ? $test->{'_prioritize_s'} : 'null';

    echo $test->ID . ' ================== <b>' . $test->post_date . ' ===== ' . $empty_or_null . '</b> <br/>';
}


Get this bounty!!!

#StackBounty: #wp-query #query #mysql #order #sql Order by summing multiple values

Bounty: 50

In a custom post type (products) I want to order products by summing multiple values based on user selection. I use custom taxonomies to filter the products.

I could use the orderby meta_value but the combinations a user could make are so many that it’s not possible to store all these totals as post meta.

I think I will need to alter the wp query using filters like posts_clauses. Could a custom SQL query handle this effective (or maybe a different approach)?

It’s a custom setup so I don’t mind how the values are stored. Could be as post meta, in a custom table or even as a json/string in the post_content field (I don’t make use of it).


Get this bounty!!!

#StackBounty: #java #mysql #spring-boot #jpa Spring JPA OneToOne FK as PK Cascade.Remove

Bounty: 50

I’ve got two tables, b and a:

  • they have a one-to-one bidirectional relationship
  • a has a foreign key to b that defines this relationship
  • this foreign key is also considered as a primary key for a, and a JPA @ID
  • I want a cascade removal that deletes the related b when a is deleted
  • in MySQL, a‘s b_id is NOT NULL

The problem is that when I delete my A object with JPA repository, I get a ConstraintViolationException on its foreign key.
I would expect that both a and b rows are deleted (cleverly starting with a‘s one).

How could I work around this knowing that I want to keep:

  • my DB schema the same
  • the cascade removal from a to b
  • the b id being the JPA @Id for a
CREATE TABLE `b` (
  `dbid` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`dbid`),
);

CREATE TABLE `a` (
  `b_id` int(11) NOT NULL,
  KEY `b_fk` (`b_id`),
  CONSTRAINT `b_fk` FOREIGN KEY (`b_id`) REFERENCES `b` (`dbid`),
);

@Entity
@Table(name = "a")
public class A {

    @Id
    @Column(name = "b_id")
    @GeneratedValue(generator = "gen")
    @GenericGenerator(name = "gen", strategy = "foreign", parameters = @Parameter(name="property", value="b"))
    private Integer bId;

    @OneToOne(cascade = CascadeType.REMOVE)
    @PrimaryKeyJoinColumn
    private B b;
}
@Entity
@Table(name = "b")
public class B {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name = "dbid")
    private Integer id;

    @OneToOne(mappedBy = "b")
    private A a;
}


Get this bounty!!!

#StackBounty: #mysql #date #date-math How can I use mysql to determine the Sundays for the last year?

Bounty: 50

I have a table where I’m currently aggregating ~200 rows per day of stats. These stats include things like # of users, how many people using X feature, etc.

What I’d like to do is query this table so summarize the data in this way:

  • Daily over the past X days
  • Weekly over the past year – Take the stats from each Sunday and pulling the current day (assuming today isn’t Sunday)
  • Monthly over the past X years – Take the stats from the last day of each month and pulling the current day (assuming today isn’t the last day of the month)

Columns:

  • date is the day the data was aggregated
  • value is the numeric value I need to show (e.g. – # of users)

I want empty records/dates to be filled with zeros so I need to use mysql to generate the dates and then join a matching record where available. How can I use mysql to determine the Sundays for the last year?

Unfortunately I can’t provide an example of what I have working at the moment, I’m still investigating potential solutions. Most queries I come across are querying records by dates, not generating the dates themselves.

My table structure:

CREATE TABLE `daily_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `stat` int(11) NOT NULL,
  `value` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


Get this bounty!!!

#StackBounty: #apache-httpd #configuration #mysql #ansible #database LAMP DB-agnostic way to create a DB stack

Bounty: 50

Is there a LAMP-MySQL (or equivalent) agnostic way to create a DB stack that includes:

  1. DB-user and a relative password
  2. DB with the same name as of the user
  3. Setting a host for the DB (say, localhost)
  4. Give the user all privileges

The reason I need such a way or approach is to have easier life when working with different LAMP-MySQL (or equivalent) DB programs as part of LAMP.

For example, not all LAMP stacks has particularly MySQL or MariaDB and the SQL syntax or DB-CLUI (Bash) extension syntax might be a tiny bit different for each DB SQL variant, hence I seek standardization in a LAMP-RDBMS agnostic fashion.


Get this bounty!!!

Apache Commons DbUtils Mini Wrapper

This is a very small DB Connector code in Java as a wrapper class to Apache DBUtils.

The Commons DbUtils library is a small set of classes designed to make working with JDBC easier. JDBC resource cleanup code is mundane, error prone work so these classes abstract out all of the cleanup tasks from your code leaving you with what you really wanted to do with JDBC in the first place: query and update data.

Some of the advantages of using DbUtils are:

  • No possibility for resource leaks. Correct JDBC coding isn’t difficult but it is time-consuming and tedious. This often leads to connection leaks that may be difficult to track down.
  • Cleaner, clearer persistence code. The amount of code needed to persist data in a database is drastically reduced. The remaining code clearly expresses your intention without being cluttered with resource cleanup.
  • Automatically populate Java Bean properties from Result Sets. You don’t need to manually copy column values into bean instances by calling setter methods. Each row of the Result Set can be represented by one fully populated bean instance.

DbUtils is designed to be:

  • Small – you should be able to understand the whole package in a short amount of time.
  • Transparent – DbUtils doesn’t do any magic behind the scenes. You give it a query, it executes it and cleans up for you.
  • Fast – You don’t need to create a million temporary objects to work with DbUtils.

DbUtils is not:

  • An Object/Relational bridge – there are plenty of good O/R tools already. DbUtils is for developers looking to use JDBC without all the mundane pieces.
  • A Data Access Object (DAO) framework – DbUtils can be used to build a DAO framework though.
  • An object oriented abstraction of general database objects like a Table, Column, or Primary Key.
  • A heavyweight framework of any kind – the goal here is to be a straightforward and easy to use JDBC helper library.

Wrapper: