Sqlx nested structure

Hi ,

type User Struct {
Id int64
Name string
Projects []Project // many to many
Emails []Email // one to many
Address Address // one to one
}

How to achieve this type of structure in sqlx.

I have solved this issue. Please give me feedback it is correct way of programming in golang .

type Installments_M struct {
Installment_ID int db:"Installment_ID"
Finyear int db:"Finyear"
College_code int db:"College_code"
Batch_code int db:"Batch_code"
Term_code int db:"Term_code"
Term_Name string db:"Term_Name"
Installment string db:"Installment"
Amount int db:"Amount"
}

type Installment_lineitem struct {
Installment_ID int db:"Installment_ID"
Lineitem int db:"Lineitem"
Finyear int db:"Finyear"
College_code int db:"College_code"
Batch_code int db:"Batch_code"
Term_code int db:"Term_code"
Term_Name string db:"Term_Name"
Fees_code int db:"Fees_code"
Fees_Name string db:"Fees_Name"
Installment string db:"Installment"
Amount int db:"Amount"
}

type Ins_MasterDetails struct {
Header Installments_M
Lineitem []Installment_lineitem
}

type Installments struct {
Installments [] Ins_MasterDetails
}

func StudentFeesInstallments(_input req.CollFinBatchAadhaar) ( resp.Installments, error, string) {

type onlyterm struct {
	Term_code int `db:"Term_code"`
}

var _MasterDetails = resp.Ins_MasterDetails{}
var _Header = []resp.Installments_M{}
var _lineItem = []resp.Installment_lineitem{}
var _Installments = resp.Installments{}

_output := onlyterm{}

_Query := `SELECT Term_code FROM fees_student_installments 
	WHERE collegecode = ?
	AND finyear = ?
	AND batch_code = ?
	AND aadhaar = ? limit 1 `

err := easyglobal.Mydb.Get(&_output, _Query,
	_input.CollegeCode, _input.Finyear, _input.BatchCode, _input.Aadhaar)
if err != nil {
	if err == sql.ErrNoRows {
		return _Installments, nil, "Term Not Configured"
	}
	return _Installments, err, ""
}

if _output.Term_code > 0 {

	_Query = `SELECT 
		M.Installment_ID,
		M.Finyear,
		M.College_code,
		M.Batch_code,
		M.Term_code,
		FT.Term_Name,
		M.Installment,
		M.Amount
		FROM fees_installment_h M,Fees_Terms FT 
	WHERE M.college_code = ?
	AND M.finyear = ?
	AND M.batch_code = ?
	AND M.Term_code = FT.Term_code
	and M.Term_code = ?
	`
	err := easyglobal.Mydb.Select(&_Header, _Query,
		_input.CollegeCode, _input.Finyear, _input.BatchCode,_output.Term_code)

	if err != nil {
		if err == sql.ErrNoRows {
			return _Installments, nil, "Record No found in Header Table "
		}
		return _Installments, err, ""
	}

	_Query = `SELECT 
		D.Installment_ID	,
		D.Lineitem		,
		D.Finyear		,
		D.College_code	,
		D.Batch_code	,
		D.Term_code	 	,
		D.Fees_code	 	,
		FH.Fees_Name	,
		D.Installment 	,
		D.Amount 		
		FROM fees_installment_d D,fees_head FH
	WHERE D.Installment_ID = ? 
	AND D.Fees_code = FH.Fees_code 
	`

	for nCtr := 0; nCtr < len(_Header); nCtr++ {
		err := easyglobal.Mydb.Select(&_lineItem, _Query, _Header[nCtr].Installment_ID)
		if err != nil {
			if err == sql.ErrNoRows {

				return _Installments, nil, "Record No found in Header Table "
			}
			return _Installments, err, ""
		}

		_MasterDetails.Header = _Header[nCtr]
		_MasterDetails.Lineitem = _lineItem

		_Installments.Installments = append(_Installments.Installments, _MasterDetails)
	}
}

return _Installments, nil, ""

}

I think this is a SQL question rather than a Go question and you may look to JOIN Projects, Emails and Adress.

https://www.dofactory.com/sql/join

JOIN Emails ON maintable_email=email_id

When using “many to many” you may use a “link-table” between main table and Project table. Though normally a project belongs to ONE customer.

And when you say one to one there is normally no extra table needed. Just extend the main table…

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.