#StackBounty: #sql #ruby-on-rails #postgresql Rails/Postgres query all records created after 5 PM in taking into account daylight savin…

Bounty: 50

I am looking to query for all records on a table with a created_at time of after 5 PM, EST WITH taking into account daylight savings.

My DB is Postgres and all timestamps are stored in UTC as a normal Rails app.

So, say I have four records

ID, created_at, time in EST (-5 offset from UTC),

1, "2017-01-01 22:46:21.829333", 5:46 PM
2, "2017-01-01 21:23:27.259393", 4:23 PM

-------- DST SWITCH -----

ID, created_at, time in EDT (-4 offset from UTC),

3, "2017-03-20 21:52:46.135713", 5:52 PM
4, "2017-06-21 20:08:53.034377", 4:08 PM

My query should return records 1 and 3, but should ignore 2 and 4.

I have tried

SELECT "id",
       "created_at"
FROM "orders"
WHERE (created_at::TIME WITHOUT TIME ZONE AT TIME ZONE 'utc' AT TIME ZONE 'US/Eastern' > ("created_at"::DATE + TIME '21:00')::TIME AT TIME ZONE 'utc' AT TIME ZONE 'US/Eastern')
ORDER BY "orders"."created_at" ASC

But this query will return record 1,2,3 when it should not return 2.


Get this bounty!!!

#StackBounty: #sql #replication #sql-server-2014 #merge-replication #data-synchronization MERGE Replication Customize synchronization c…

Bounty: 50

I’m trying to implement a MERGE Replication between just two servers. A publisher and only one Subscriber.

Assume these tables :

--Main table in Publisher
Ticket (id, userId, reqId, blockedDate, usedDate, returnDate, transferDate, ...)

--On Subscriber (The fields are in Publication)
Ticket (id, userId, blockedDate, usedDate)

We must assign current date to transferDate whenever a row goes to Subscriber, and assign to returnDate whenever it comes back from Subscriber. As we HAVE to consider a file syncing(transfer/return data to/from subscriber with a USB stick!) these fields must be assigned on syncing either on file syncing or on Replication.

I know you can add your custom business logic for Conflict Resolution(when conflict occurs). I’m looking for something like triggers for Replication events which gain me the ability to manipulate some fields out of Publication(or even another table whenever needed).

Questions

  • Is it possible to handle replication events(push and pull) manually or customize or override them?

  • What is the overhead/performance of each these ways? Managing syncing with a SP(via a distributed transaction) , and implementing a MERGE Replication?

UPDATE
I need synchronization to be scheduled(at mid night when there’s no any load on DB server), BUT it is highly needed we’ll be able to sync manually(from within our web application).


Get this bounty!!!

#StackBounty: #sql-server #sql #sql-server-2016 #sql-server-2017 Bug in Rebuild on SQL Server 2016 and above?

Bounty: 50

EDIT:

Question Summary:
A fragmented Clustered Index is not performing well, even after a REBUILD of the Index. If the index is REORGANIZED then performance increases for the given table/index.

I am seeing this unusual behavior only on SQL Server 2016 and above, I have tested this scenario on different hardware and different versions(All personal machines and all have the conventional rotating hard disk ).
Let me know if need any more information.

Is this a bug in SQL Server 2016 and onwards?

I can provide the complete details and analysis with the script if anybody wants, but not providing right now because the script quite large and will take a lot of space in the question.
Please test the shorter version of sample script taken from the provided link below in your DEV environment if you have SQL Server 2016 and above.

**SCRIPT:**
-- SECTION 1
/*
Create a Test Folder in the machine and spefiy the drive in which you created
*/
USE MASTER

CREATE DATABASE RebuildTest
ON 
( NAME = 'RebuildTest',
    FILENAME = 'F:TESTRebuildTest_db.mdf',
    SIZE = 200MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB )
LOG ON
( NAME = 'RebuildTest_log',
    FILENAME = 'F:TESTRebuildTest_db.ldf',
    SIZE = 100MB,
      MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB ) ;
GO

BEGIN TRAN

USE RebuildTest

select  top 1000000
row_number () over ( order by (Select null)) n into Numbers from
sys.all_columns  a cross  join  sys.all_columns

CREATE TABLE [DBO].FRAG3 (
Primarykey int NOT NULL ,
SomeData3 char(1000) NOT NULL )

ALTER TABLE DBO.FRAG3
ADD CONSTRAINT PK_FRAG3 PRIMARY KEY (Primarykey)

INSERT INTO [DBO].FRAG3
SELECT n , 'Some text..'
FROM Numbers
Where N/2 = N/2.0

Update DBO.FRAG3 SET Primarykey =  Primarykey-500001
Where  Primarykey>500001

COMMIT

 -- SECTION 2

SELECT @@VERSION

/*                                                       BEGIN PART FRAG1.1     */

----- BEGIN CLEANBUFFER AND DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF

----- END CLEANBUFFER AND DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.2: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

--BEGIN REORGANIZE the Index
ALTER INDEX ALL ON [DBO].[FRAG3] REORGANIZE ;
--END REORGANIZE the Index

----- BEGIN CLEANBUFFER  FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------END PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

Results:
enter image description here

enter image description here

enter image description here

enter image description here

Crystal Disk Mark Test Results:

enter image description here

I will add more later.

Detail:
I am seeing an unusual behavior of storage engine(maybe) on SQL Server 2016 and above, I have created a highly fragmented table for (read problems with fragmentation) demo purpose then rebuilt it,

Even after rebuilding the index performance doesn’t increase as expected.
To make sure data access pattern should be in key order not in IAM Driven (Allocation Order Scan), I used the range predicate.

Initially, I thought may be SQL Server 2016 and above is more aggressive for large Scan, to check that I adjusted the page count and row count but the performance pattern doesn`t change, I tested all on a personal system so I can say there was no other user activity was going on,
I have tested this behavior on other hardware as well (all have traditional rotating hard disks) Performance patterns are almost same.

I have checked wait stats all seem normal only PAGELATCH_IO(using Paul Randal script) there. I checked data pages using DMV sys.dm_db_database_page_allocations it also seems ok.
But If I Reorganize the table or move all the data to a new table with same index definition disk IO performance increases, I have checked this with
perfmon and it seems like reorganizing index /new table can leverage sequential IO and the rebuild index still using the random reads in spite of the fact that both have almost same data pages internal and external fragmentation.

I am attaching the complete query with the results on my system I captured.
if you guys have SQL Server 2016 and above DEV box, please check this and share your results.

WARNING: This test consists some undocumented command and DROPCLEANBUFFERS so not at all run at the production server.

If this is really a bug I think I should file it.

So the question is: Is it really a bug or I am missing something 😉

LINKS: (PASTEBIN)

1 Fragmented Table Creation

2 Supporting SPs RUN AFTER TABLE CREATION

3 Test

4 Any Problem With Rebuild

5 Put Data in New Table

LINK:(GOOGLE DRIVE)

6 Download Result ON My SYSTEMS


Get this bounty!!!

#StackBounty: #sql-server #sql #stored-procedures #type-conversion Conversion error on paramater but not variable

Bounty: 50

I’ve got a stored procedure that accepts a parameter;

CREATE PROCEDURE [dbo].[Stored_Proc_Name] (@ParamID NVARCHAR(255) = NULL)

I call this stored procedure and pass in a uniqueidentifier, something like this;

EXEC [Stored_Proc_Name] 'a6ed99c1-29c8-43f4-9e3a-0065e6dc7fc1'

The stored procedure does a bit of XML processing and returns a result set, it’s fairly simple.

For a few paramaters that are passed I’m getting a type conversion error;

Msg 8114, Level 16, State 5, Procedure Stored_Proc_Name, Line 0
Error converting data type nvarchar to int.

I initially thought that this was an issue with the data but the issue resolves itself if i declare a local variable within the stored procedure, like this and then use that local variable in place of the parameter. The only places that this parameter is used is within WHERE clauses within my stored proc.

CREATE PROCEDURE [dbo].[Stored_Proc_Name] (@ParamID NVARCHAR(255) = NULL)
AS
DECLARE @localID nvarchar(255)
SET @localID = @id

The issue is resolved and the procedure runs fine.

Any ideas on the next step of the investigation or something obvious that I’ve missed?


Get this bounty!!!

#StackBounty: #sql #csv #directory #export Sql Query results to CSV to a specified file path after stored procedure execution

Bounty: 50

I’m trying to execute a stored procedure (which i know works) in T-SQL that then gets those results into a CSV file and puts that file into a directory. I’m not sure how to formulate that query, exactly though. Here’s what i’ve tried thus far to no avail:

EXECUTE CLR_ExportQueryToCSV @QueryCommand = 'execute databaseName.dbo.StoredProcedureName',
                          @FilePath = 'C:Directory',
                          @FileName = 'FileToExport.csv',
                          @IncludeHeaders = 1

I realize CLR_ExportQueryToCSV doesn’t exist. Is there any system stored procedure that will do what i’m wanting?


Get this bounty!!!

#StackBounty: #sql #mysql #sync Query to determine insert / update operations to synchronize data

Bounty: 100

I’ve been developing a system where the mobile syncs data from the server.The phone can run offline but when connected to the internet it needs take care of inserted or updated data.

Here is the output result (this is just for demonstration purposes it will be changed a bit):

[
    {
        "version_send": "1",
        "action_peformed": "insert,update",
        "version": "1,4",
        "change_date": "2017-06-22 16:42:03",
        "audit_name": "Push Ups",
        "current_name": "Pushup",
        "id_exercise": "1",
        "action_peformed_": "update"
    },
    {
        "version_send": "1",
        "action_peformed": "insert",
        "version": "1",
        "change_date": "2017-06-22 16:42:06",
        "audit_name": "Squat",
        "current_name": "Squat",
        "id_exercise": "2",
        "action_peformed_": "igonre"
    },
    {
        "version_send": "1",
        "action_peformed": "insert",
        "version": "1",
        "change_date": "2017-06-22 16:42:09",
        "audit_name": "Chin Ups",
        "current_name": "Chin Ups",
        "id_exercise": "3",
        "action_peformed_": "igonre"
    },
    {
        "version_send": "1",
        "action_peformed": "insert,update",
        "version": "2,3",
        "change_date": "2017-06-22 16:44:25",
        "audit_name": "Pull Ups",
        "current_name": "Pull Up",
        "id_exercise": "4",
        "action_peformed_": "insert"
    },
    {
        "version_send": "1",
        "action_peformed": "insert",
        "version": "2",
        "change_date": "2017-06-22 16:45:08",
        "audit_name": "Sit Up",
        "current_name": "Sit Up",
        "id_exercise": "5",
        "action_peformed_": "insert"
    },
    {
        "version_send": "1",
        "action_peformed": "insert,update,update",
        "version": "2,3,4",
        "change_date": "2017-06-22 16:45:28",
        "audit_name": "Pike Push Up",
        "current_name": "Pike Pushups",
        "id_exercise": "6",
        "action_peformed_": "insert"
    }
]

The goal here was to determine if the phones version if for update or for insert of the new data.

For example:

{
        "version_send": "1",
        "action_peformed": "insert,update,update",
        "version": "2,3,4",
        "change_date": "2017-06-22 16:45:28",
        "audit_name": "Pike Push Up",
        "current_name": "Pike Pushups",
        "id_exercise": "6",
        "action_peformed_": "insert"
    }

The data was “action_peformed”: “insert,update,update” but the phone has the version 1, and the first insert on that data was from version two so the output action be insert.

But if the phone version was 2 it means that the action should be insert..
You get the point.

The current query that does this looks like this:

SELECT 
        :version as `version_send`,
        GROUP_CONCAT(ea.`action_peformed` ORDER BY ea.`action_peformed` ASC) as `action_peformed`,
        GROUP_CONCAT(ea.`version` ORDER BY ea.`version` ASC) as `version`,
        ea.change_date,
        ea.name as audit_name,
        e.name as current_name,
        e.id_exercise ,
        ##BEGIN Determine what should be done
        IF (:version < MAX(ea.`version`),
           IF(
                FIND_IN_SET (:version, 
                    GROUP_CONCAT( DISTINCT ea.`version` SEPARATOR ',' )  
                    ), 
                'update',##'is less than max but has one or more versions - update',

                IF (:version > MIN(ea.`version`),
                'update',##'is less than max but has one or more versions - update',
                'insert'##'is less than max but has no versions - insert'
                )
            )
        ,'igonre') as `action_peformed_`
        ##END Determine what should be done*/
        FROM exercise_audit AS ea LEFT JOIN exercise AS e ON e.id_exercise = ea.reference_id GROUP BY e.id_exercise

And here are the tables that i used:

CREATE TABLE `exercise` (
  `id_exercise` int(11) NOT NULL,
  `name` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `exercise_audit` (
  `id_exercise_audit` int(11) NOT NULL,
  `name` varchar(45) NOT NULL COMMENT 'The name will take the new value if inserted ,or the old value if edited.And the latest value if deleted.',
  `action_peformed` varchar(45) NOT NULL,
  `version` int(11) NOT NULL,
  `reference_id` int(11) NOT NULL,
  `change_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `version` (
  `id_version` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `state` enum('C','P') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Showing rows 0 – 5 (6 total, Query took 0.0010 seconds.)

I feel that the query is missing something, how could i improve it?

I someone is interested i can share the code over github, if it will be easier.


Get this bounty!!!

#StackBounty: #sql #mysql #time-limit-exceeded #laravel Laravel query to select photo orders related to some competition

Bounty: 50

I’m using Laravel and I have one monster query that returns exactly what I need, and runs perfectly on my dev machine, but on production runs extremely slow and I get timeout errors.

Here’s the query:

SELECT *
FROM `order_photos`
WHERE EXISTS (
        SELECT *
        FROM `orders`
        WHERE `order_photos`.`order_id` = `orders`.`id`
            AND `is_completed` = '1'
        )
    AND (
        EXISTS (
            SELECT *
            FROM `athletes`
            WHERE `order_photos`.`athlete_id` = `athletes`.`id`
                AND (
                    EXISTS (
                        SELECT *
                        FROM `photos`
                        INNER JOIN `athlete_photos` ON `photos`.`id` = `athlete_photos`.`photo_id`
                        WHERE `athletes`.`id` = `athlete_photos`.`athlete_id`
                            AND `partner_id` = '1'
                            AND EXISTS (
                                SELECT *
                                FROM `albums`
                                WHERE `photos`.`album_id` = `albums`.`id`
                                    AND `competition_id` = order_photos.competition_id
                                )
                            AND `is_published` = '1'
                        )
                    )
            )
        OR EXISTS (
            SELECT *
            FROM `photos`
            WHERE `order_photos`.`id` = `photos`.`order_photo_id`
                AND `partner_id` = '1'
                AND EXISTS (
                    SELECT *
                    FROM `albums`
                    WHERE `photos`.`album_id` = `albums`.`id`
                        AND `competition_id` = order_photos.competition_id
                    )
            )
        )

Here’s my laravel code that generates this query:

$preorders = OrderPhoto::whereHas('order', function ($order) {
    $order->completed();
})
    ->where(function ($query) use ($partner) {
        $query->whereHas('athlete', function ($athlete) use ($partner) {
            $athlete->where(function ($query) use ($partner) {
                $query->whereHas('photos', function ($photo) use ($partner) {
                    $photo->where('partner_id', $partner->id);
                    $photo->whereHas('album', function ($album) {
                        $album->where('competition_id', DB::raw('order_photos.competition_id'));
                    });
                });
            });
        });
        $query->orWhereHas('photos', function ($photo) use ($partner) {
            $photo->where('partner_id', $partner->id);
            $photo->whereHas('album', function ($album) {
                $album->where('competition_id', DB::raw('order_photos.competition_id'));
            });
        });
    })
    ->with('competition')
    ->get();

Question is, should I add indexes to speed this up, or should I take a different approach? My photos table is the only one with a ton of records, over a million. I’m guessing that those sub-queries are expensive. Again, the output is exactly what I need and perfectly represents the relationships that must be this exact search.

The only way I can think of to take a different approach is to break this up into multiple queries and then combine/filter the results in php, which would be a bummer because this one query returns exactly what I need in the form I need it in without any further processing.

Edit: here’s the explains:

 - EXPLAIN #1: `order_photos` (PRIMARY)

Params
id  1
select_type PRIMARY
table   order_photos
partitions  null
type    ALL
possible_keys   null
key null
key_len null
ref null
rows    24
filtered    100
Extra   Using where

 - EXPLAIN #6: `photos` (DEPENDENT SUBQUERY)

Params
id  6
select_type DEPENDENT SUBQUERY
table   photos
partitions  null
type    ref
possible_keys   photos_partner_id_index,photos_order_photo_id_index
key photos_order_photo_id_index
key_len 5
ref llspark.order_photos.id
rows    31
filtered    100
Extra   Using index condition; Using where

 - EXPLAIN #7: `albums` (DEPENDENT SUBQUERY)

Params
id  7
select_type DEPENDENT SUBQUERY
table   albums
partitions  null
type    eq_ref
possible_keys   PRIMARY,albums_competition_id_index
key PRIMARY
key_len 4
ref llspark.photos.album_id
rows    1
filtered    10
Extra   Using where

 - EXPLAIN #3: `athletes` (DEPENDENT SUBQUERY)

Params
id  3
select_type DEPENDENT SUBQUERY
table   athletes
partitions  null
type    eq_ref
possible_keys   PRIMARY
key PRIMARY
key_len 4
ref llspark.order_photos.athlete_id
rows    1
filtered    100
Extra   Using where; Using index

 - EXPLAIN #4: `athlete_photos` (DEPENDENT SUBQUERY)

Params
id  4
select_type DEPENDENT SUBQUERY
table   athlete_photos
partitions  null
type    ALL
possible_keys   athlete_photos_athlete_id_index,athlete_photos_photo_id_index
key null
key_len null
ref null
rows    7
filtered    14.285715103149414
Extra   Using where

 - EXPLAIN #4: `photos` (DEPENDENT SUBQUERY)

Params
id  4
select_type DEPENDENT SUBQUERY
table   photos
partitions  null
type    eq_ref
possible_keys   PRIMARY,photos_partner_id_index
key PRIMARY
key_len 4
ref llspark.athlete_photos.photo_id
rows    1
filtered    10
Extra   Using where

 - EXPLAIN #5: `albums` (DEPENDENT SUBQUERY)

Params
id  5
select_type DEPENDENT SUBQUERY
table   albums
partitions  null
type    eq_ref
possible_keys   PRIMARY,albums_competition_id_index
key PRIMARY
key_len 4
ref llspark.photos.album_id
rows    1
filtered    10
Extra   Using where

 - EXPLAIN #2: `orders` (DEPENDENT SUBQUERY)

Params
id  2
select_type DEPENDENT SUBQUERY
table   orders
partitions  null
type    eq_ref
possible_keys   PRIMARY
key PRIMARY
key_len 4
ref llspark.order_photos.order_id
rows    1
filtered    10
Extra   Using where


Get this bounty!!!

#StackBounty: #mysql #sql #mariadb #partitioning Does partitioning help with lookups and inserts/updates by primary key?

Bounty: 50

I’m using mariadb 10.1 and the default innodb storage, and I have a few tables with currently 10 to 100 million rows. These tables will keep growing a few million per month, and it’s mostly caching.

They either have a single primary key (bigint) or a composite primary key (2 bigints) and no autoincrement, and I always insert, select or update by primary key. I also do a lot of joins by primary key, or selects WHERE PK IN (1, 2, 3, 4...).

Also, these tables receive lots of updates per hour, and I usually update them in batches of 5000 or 10000 at a time. We have more inserts and updates than selects for some of these tables.

I have 3 questions:

  1. It seems to me that simple selects by PK returning 1 row (select x, y from table where pk = 123) will have no actual difference in performance with partitioning. Is that right?

  2. What about joins or selects as WHERE PK IN(SELECT PK FROM ...)? Will it cause more scans to join a partitioned table than a single table?

  3. Considering I usually do a lot of concurrent batches (multiple servers may send data at the same time) using:

INSERT INTO X VALUES (1, 'A'), (2, 'B'), ... ON DUPLICATE KEY UPDATE ...

or

REPLACE INTO X VALUES(1, 'A'), (2, 'B'),...

will partitioning help with concurrent inserts and updates, say by being able to affect multiple partitions at the same time?

Thanks in advance.


Get this bounty!!!

#StackBounty: #mysql #sql #sql-server #hibernate #hql How can I update an interval of dates in the database (Hibernate HQL)

Bounty: 50

Consider, for some specific database entry of id id, an interval of dates in the database, from BEGIN_DATE to END_DATE, denoted by (BEGIN_DATE,END_DATE).

Given two new dates, that form the interval (newBeginDate, newEndDate), I must update (BEGIN_DATE,END_DATE) so that the resulting interval contains all 4 dates. In other words, I want to add the two intervals (And since I am adding them, the resulting interval may remain the same or grow, but never shrink).

A few examples:

If (BEGIN_DATE,END_DATE) = (January 10,January 20), then:

  • (BEGIN_DATE,END_DATE) + (Jan 15,Jan 25) = (Jan 10,Jan 25)

  • (BEGIN_DATE,END_DATE) + (Jan 05,Jan 15) = (Jan 05,Jan 20)

  • (BEGIN_DATE,END_DATE) + (Jan 05,Jan 25) = (Jan 05,Jan 25)

  • (BEGIN_DATE,END_DATE) + (Jan 15,Jan 15) = (Jan 10,Jan 20)

  • (BEGIN_DATE,END_DATE) + (Jan 10,Jan 13) = (Jan 10,Jan 20)

The two following queries together achieve this goal. The first one updates BEGIN_DATE, and the second updates END_DATE:

session.createQuery(
   "update APPOINTMENTS " +
     "set BEGIN_DATE = :newBeginDate " +
     "where " +
     "(BEGIN_DATE is null or BEGIN_DATE > :newBeginDate) " +
     "and ID = :id ")
           .setParameter("newBeginDate", newBeginDate)
           .setParameter("id", id)
           .executeUpdate();

session.createQuery(
   "update APPOINTMENTS " +
     "set END_DATE = :newEndDate " +
     "where " +
     "(END_DATE is null or END_DATE < :newEndDate) " +
     "and ID = :id ")
        .setParameter("newEndDate", newEndDate)
        .setParameter("id", id)
        .executeUpdate();

My question is:
How can do it in a single query?


Get this bounty!!!

#StackBounty: #sql #database-design #relational-theory Database Design Review (SQL) – coming over from MongoDB

Bounty: 50

I am building a website as a personal project just for some experience in a new web framework and want to make sure I model my SQL database in a good way with the appropriate relationships before building it out and getting myself in too deep. I’m coming over from using MongoDB so this will be my first application in which I have to model the SQL database.

This application will allow users to join and create groups for discussion, add categories to the groups and then post topics to a specific category in a group.

My application will have 4 main things -> Users, Groups, Categories and Topics (Assuming no replies to topics, just a simple posting for users to read).

This is what I have so far:

FK = Foreign Key

User
-id
-username
-password

Group
-id
-title
-description
-owner(FK user-id)

Category
-id
-title
-group(FK group-id)

Topic
-id
-title
-author(FK user-id)
-category(FK category-id)

Membership
-id
-group(FK group-id)
-user(FK user-id)

I am in no capacity a database expert and took one course on database a year ago so any feedback is appreciated


Get this bounty!!!